{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Synthesize search sessions from signals\n",
    "\n",
    "This notebook synthesizes search sessions from the CTR of the clicked documents on each search result. It's assumed that if you order results by CTR, that roughly captures the source search system's relevance ranking in aggregate (including all the position and other biases). \n",
    "\n",
    "You can then check to see if the document is above or below average for that rank position (using a z score). You can then use that z score to translate that document to any other position. \n",
    "\n",
    "This is intended more for creating fake search session data for examples in AI Powered Search, and not a replacement for actually logging real search sessions in your search system."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 175,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\"query_id\",\"user\",\"type\",\"target\",\"signal_time\"\r\n",
      "\"u2_0_1\",\"u2\",\"query\",\"nook\",\"2019-07-31 08:49:07.3116\"\r\n",
      "\"u2_1_2\",\"u2\",\"query\",\"rca\",\"2020-05-04 08:28:21.1848\"\r\n",
      "\"u3_0_1\",\"u3\",\"query\",\"macbook\",\"2019-12-22 00:07:07.0152\"\r\n",
      "\"u4_0_1\",\"u4\",\"query\",\"Tv antenna\",\"2019-08-22 23:45:54.1030\"\r\n",
      "\"u5_0_1\",\"u5\",\"query\",\"AC power cord\",\"2019-10-20 08:27:00.1600\"\r\n",
      "\"u6_0_1\",\"u6\",\"query\",\"Watch The Throne\",\"2019-09-18 11:59:53.7470\"\r\n",
      "\"u7_0_1\",\"u7\",\"query\",\"Camcorder\",\"2020-02-25 13:02:29.3089\"\r\n",
      "\"u9_0_1\",\"u9\",\"query\",\"wireless headphones\",\"2020-04-26 04:26:09.7198\"\r\n",
      "\"u10_0_1\",\"u10\",\"query\",\"Xbox\",\"2019-09-13 16:26:12.0132\"\r\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py:3049: DtypeWarning: Columns (3) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    }
   ],
   "source": [
    "! cd ../../data/retrotech && head signals.csv\n",
    "\n",
    "import random\n",
    "import pandas \n",
    "import numpy\n",
    "import sys\n",
    "sys.path.append('../..')\n",
    "from aips import *\n",
    "import os\n",
    "from IPython.display import display,HTML\n",
    "\n",
    "signals = pandas.read_csv('../../data/retrotech/signals.csv')\n",
    "\n",
    "#seed=8675309\n",
    "#random.seed(seed)\n",
    "#numpy.random.seed(seed)\n",
    "\n",
    "DOCS_PER_SESSION=30 # how many docs in one search page view?\n",
    "NUM_SESSIONS=5000 # how many sessions to generate for each query?\n",
    "\n",
    "# Generate search sessions for these queries\n",
    "QUERIES_TO_SIMULATE=['dryer', 'iphone', 'nook', 'kindle', \n",
    "                     'lcd tv', 'ipad', 'headphones', 'macbook',\n",
    "                     'how i met your mother', 'star wars', 'star trek',\n",
    "                     'blue ray', 'bluray']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Gather queries occuring above a certain threshold"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 176,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:8: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n",
      "  \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>query</th>\n",
       "      <th>query_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>104</th>\n",
       "      <td>blue ray</td>\n",
       "      <td>276</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        query  query_id\n",
       "104  blue ray       276"
      ]
     },
     "execution_count": 176,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MIN_QUERY_EVENTS=100\n",
    "\n",
    "queries = signals[signals['type'] == 'query']\n",
    "popular_queries = queries.groupby('target').count() \\\n",
    "                         .rename(columns={'query_id': 'query_count'}) \\\n",
    "                         .sort_values('query_count', ascending=False)\n",
    "popular_queries = popular_queries[popular_queries['query_count'] > MIN_QUERY_EVENTS].index.to_list()\n",
    "pop_query_events = signals[signals['type'] == 'query'][signals['target'].isin(popular_queries)]\n",
    "query_events = pop_query_events[['query_id', 'target']].rename(columns={'target': 'query'})\n",
    "\n",
    "# Cleanup by lowercasing\n",
    "# This step has its pros and cons. We might miss some info that case gives us, but it also\n",
    "# aggregates more signal per query\n",
    "query_events['query'] = query_events['query'].apply(lambda q: q.lower())\n",
    "query_event_counts = query_events.groupby('query')['query_id'].count().reset_index().sort_values('query_id', ascending=False)\n",
    "query_event_counts[query_event_counts['query'].str.contains('blue ray')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Join click events with corresponding queries into one table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 177,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>query_id</th>\n",
       "      <th>query</th>\n",
       "      <th>clicked_doc_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>u2_0_1</td>\n",
       "      <td>nook</td>\n",
       "      <td>9781400532650</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>u2_1_2</td>\n",
       "      <td>rca</td>\n",
       "      <td>883393001119</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>u3_0_1</td>\n",
       "      <td>macbook</td>\n",
       "      <td>885909464036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>u4_0_1</td>\n",
       "      <td>tv antenna</td>\n",
       "      <td>079000334835</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>u7_0_1</td>\n",
       "      <td>camcorder</td>\n",
       "      <td>027242821866</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488539</th>\n",
       "      <td>u744327_0_1</td>\n",
       "      <td>dre beats</td>\n",
       "      <td>848447000135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488540</th>\n",
       "      <td>u744328_0_1</td>\n",
       "      <td>sirius radio</td>\n",
       "      <td>884720004032</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488541</th>\n",
       "      <td>u744330_0_1</td>\n",
       "      <td>usb drive</td>\n",
       "      <td>718037770604</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488542</th>\n",
       "      <td>u744331_0_1</td>\n",
       "      <td>iphone 4s</td>\n",
       "      <td>885909538027</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>488543</th>\n",
       "      <td>u744334_0_1</td>\n",
       "      <td>ds games</td>\n",
       "      <td>47875843660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>488544 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           query_id         query clicked_doc_id\n",
       "0            u2_0_1          nook  9781400532650\n",
       "1            u2_1_2           rca   883393001119\n",
       "2            u3_0_1       macbook   885909464036\n",
       "3            u4_0_1    tv antenna   079000334835\n",
       "4            u7_0_1     camcorder   027242821866\n",
       "...             ...           ...            ...\n",
       "488539  u744327_0_1     dre beats   848447000135\n",
       "488540  u744328_0_1  sirius radio   884720004032\n",
       "488541  u744330_0_1     usb drive   718037770604\n",
       "488542  u744331_0_1     iphone 4s   885909538027\n",
       "488543  u744334_0_1      ds games    47875843660\n",
       "\n",
       "[488544 rows x 3 columns]"
      ]
     },
     "execution_count": 177,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clicks = signals[signals['type'] == 'click']\n",
    "click_events = clicks[['query_id', 'target']].rename(columns={'target': 'clicked_doc_id'})\n",
    "clicks_per_query = query_events.merge(click_events, \n",
    "                                      on='query_id', \n",
    "                                      how='left')\n",
    "clicks_per_query['clicked_doc_id'] = clicks_per_query['clicked_doc_id'].fillna(0)\n",
    "# clicks_per_query.groupby('query').count().sort_values('query_id', ascending=False)\n",
    "\n",
    "clicks_per_query"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compute a CTR for each query/doc pair"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 178,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.15889705213356384\n",
      "0.10964912280701754\n",
      "0.07842453816660858\n",
      "0.059931506849315065\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x7faab0d88310>"
      ]
     },
     "execution_count": 178,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAD4CAYAAAAXUaZHAAAABHNCSVQICAgIfAhkiAAAAAlwSFlzAAALEgAACxIB0t1+/AAAADh0RVh0U29mdHdhcmUAbWF0cGxvdGxpYiB2ZXJzaW9uMy4yLjEsIGh0dHA6Ly9tYXRwbG90bGliLm9yZy+j8jraAAAU3UlEQVR4nO3df6zdd13H8eebldrBxXVzcFc65i1mso5dMeyKCIr3OomDEYsJzLkJhcw0KL9ihqHFxGHMwoyRiE5iGrashh+XOqar4ICl5DoNDGwFKV2BzbUr3WAV2IZ3spVub/84547Tu3N7zznf8+Oez3k+kuae7+/Pp7d5nU8/38/3843MRJJUlqcNugCSpO4z3CWpQIa7JBXIcJekAhnuklSgVYMuAMCZZ56ZExMTHR37yCOP8MxnPrO7BVrhrPNosM6joUqd9+7d+93MfHazbSsi3CcmJtizZ09Hx87NzTE9Pd3dAq1w1nk0WOfRUKXOEXHvUtvslpGkAhnuklQgw12SCmS4S1KBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAKtiCdUh9XE1k8tue3QtZf0sSSSdCJb7pJUoGXDPSJuiIijEfG1hnV/ERFfj4ivRsQ/RsTahm3bIuLuiPhGRPxGrwouSVpaKy33G4GLF627DbggM38O+CawDSAizgcuA15YP+aDEXFK10orSWrJsuGembcD31+07rOZeby+eAdwdv3zJmA2Mx/LzIPA3cBLulheSVILIjOX3yliAvhkZl7QZNs/Ax/PzA9HxHXAHZn54fq264FbM/OmJsdtAbYAjI+PXzg7O9tRBebn5xkbG+vo2Kr23ffwktsm15/Ws+sOss6DYp1Hg3Vuz8zMzN7MnGq2rdJomYj4Y+A48JGFVU12a/rtkZnbge0AU1NT2el8xoOc//lNJxstc8V0z67rnNejwTqPhl7VueNwj4jNwGuAi/LHzf8jwPMadjsbuL/z4kmSOtFRuEfExcC7gV/NzP9r2LQL+GhEvB94LnAu8KXKpRywk41nl6SVaNlwj4iPAdPAmRFxBLia2uiYnwBuiwio9bO/JTP3R8RO4E5q3TVvzczHe1V4SVJzy4Z7Zv5Ok9XXn2T/a4BrqhRKklSNT6hKUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuklQg38TUI0s91eobmiT1gy13SSqQ4S5JBTLcJalAhrskFchwl6QCGe6SVCDDXZIKZLhLUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuklQgw12SCmS4S1KBln1ZR0TcALwGOJqZF9TXnQF8HJgADgGXZuaD9W3bgCuBx4F3ZOZnelLyHljqBRuSNGxaabnfCFy8aN1WYHdmngvsri8TEecDlwEvrB/zwYg4pWullSS1ZNmWe2beHhETi1ZvAqbrn3cAc8C76+tnM/Mx4GBE3A28BPhCd4o7/Hz9nqR+iMxcfqdauH+yoVvmocxc27D9wcw8PSKuA+7IzA/X118P3JqZNzU55xZgC8D4+PiFs7OzHVVgfn6esbGxjo5dbN99D3flPJ2YXH9ay/t2s87DwjqPBuvcnpmZmb2ZOdVsW7dfkB1N1jX99sjM7cB2gKmpqZyenu7ognNzc3R67GJvGmCf+6Erplvet5t1HhbWeTRY5+7pdLTMAxGxDqD+82h9/RHgeQ37nQ3c33nxJEmd6DTcdwGb6583A7c0rL8sIn4iIjYA5wJfqlZESVK7WhkK+TFqN0/PjIgjwNXAtcDOiLgSOAy8HiAz90fETuBO4Djw1sx8vEdllyQtoZXRMr+zxKaLltj/GuCaKoWSJFXjE6qSVKBuj5ZRhxz/LqmbbLlLUoEMd0kqkOEuSQUy3CWpQIa7JBXIcJekAhnuJ3FozeUcWnP5oIshSW0z3CWpQIa7JBXIcJekAhnuklQgw12SCmS4S1KBDHdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIMNdkgpkuEtSgQx3SSqQ4S5JBaoU7hHxhxGxPyK+FhEfi4g1EXFGRNwWEXfVf57ercJKklrTcbhHxHrgHcBUZl4AnAJcBmwFdmfmucDu+rIkqY+qdsusAk6NiFXAM4D7gU3Ajvr2HcBrK15DktSmyMzOD454J3AN8EPgs5l5RUQ8lJlrG/Z5MDOf0jUTEVuALQDj4+MXzs7OdlSG+fl5xsbGOjp2sX33PXzC8uTTDtbWP7GhK+fvxOT6056yrpt1HhbWeTRY5/bMzMzszcypZts6Dvd6X/ongN8GHgL+AbgJuK6VcG80NTWVe/bs6agcc3NzTE9Pd3TsYhNbP3XC8sLLsSce/WhXzt+JQ9de8pR13azzsLDOo8E6tycilgz3Kt0yvw4czMz/ycwfATcDLwMeiIh19QuvA45WuIYkqQNVwv0w8NKIeEZEBHARcADYBWyu77MZuKVaESVJ7VrV6YGZ+cWIuAn4T+A48GVgOzAG7IyIK6l9Aby+GwWVJLWu43AHyMyrgasXrX6MWitekjQgPqEqSQWq1HIv0cIIGUkaZrbcJalAIxfukzsmB10ESeq5kQt3SRoFhrskFcgbqgDvrc3fcmhNe4c13nwd5BQFkrSYLXdJKpDhLkkFMtwlqUCGuyQVyHCXpAIZ7pJUIIdCtsAhj5KGzUi23Cd3TDoNgaSijWS4S1LpRrpbZqH1vm+p7RvOqW0/eLij89udI2lQbLlLUoFGNtx3vu94V893aM3lvuhD0oox0t0yneh3gE9s/dRT1l01eZzpvpZC0rAZ2Za7JJXMcJekAhnuklSgkQv3bt9IlaSVaOTCfSkHZp876CJIUtdUCveIWBsRN0XE1yPiQET8UkScERG3RcRd9Z+nd6uwkqTWVG25fwD4dGaeB7wIOABsBXZn5rnA7vryinDgvI2DLoIk9UXH4R4RPwm8ArgeIDOPZeZDwCZgR323HcBrqxZSktSeyMzODoz4eWA7cCe1Vvte4J3AfZm5tmG/BzPzKV0zEbEF2AIwPj5+4ezsbEflmJ+fZ2xsrKV9H92/H4B7zgqe/53knrMCgPOPHePR7z+dNWf86Ml971y9+snP5x871nJ59j2x4cnPk0872HR9VeOnwnPOOK1r5xsG7fyeS2GdR0OVOs/MzOzNzKlm26qE+xRwB/DyzPxiRHwA+AHw9lbCvdHU1FTu2bOno3LMzc0xPT3d0r4L3TKXblvFzvcd59JttQd09x08/OQN1Y2X3Q/8eNKwhe2tapwgrFcTh101eZy3X7Gpa+cbBu38nkthnUdDlTpHxJLhXqXP/QhwJDO/WF++CXgx8EBErKtfeB1wtMI1JEkd6DjcM/M7wLci4gX1VRdR66LZBWyur9sM3FKphH02ueGcE1rtkjSMqk4c9nbgIxGxGrgHeDO1L4ydEXElcBh4fcVrSJLaVCncM/MrQLP+nouqnFeSVI1PqEpSgUZyPvdezi/Tr/nem83zDnDo2kv6cn1JK9tIhvuChZCf3HYOO+nPhGK+V1VSP9gtI0kFKqLlfuC8jWz8+oFl9xkk368qqZ9suUtSgQz3FvhQk6RhY7hLUoEMd0kqkOG+iO9YlVQCw12SCmS4S1KBDPcm7JqRNOwMd0kqkOEuSQUy3CWpQEXMLVMCZ4uU1E2GO2XdQHWed0lgt4wkFclwb9PkhnOcSEzSime4S1KBDHdJKpA3VFu0uCtmcsM57Dt4uNI5fTuTpF6x5S5JBaoc7hFxSkR8OSI+WV8+IyJui4i76j9Pr15MSVI7utFyfyfQ+HbqrcDuzDwX2F1fliT1UaVwj4izgUuADzWs3gTsqH/eAby2yjVWsoVhkQ6NlLTSRGZ2fnDETcD7gGcB78rM10TEQ5m5tmGfBzPzKV0zEbEF2AIwPj5+4ezsbEdlmJ+fZ9W997LmhS886X6P7t/f1nnvOSva2v/8Y8fa2v9k9j2x4aTbx0+FB37YnWtNrj+teRnue7it/Xttfn6esbGxgVx7UKzzaKhS55mZmb2ZOdVsW8ejZSLiNcDRzNwbEdPtHp+Z24HtAFNTUzk93fYpAJibm2P8b65j49cPnHS/A2/5/bbO+0fb2vurqTpyptGblplb5qrJ4/zlvu4MdDp0xXTzMiw1jcES+/fa3Nwcnf4bGVbWeTT0qs5VEuLlwG9GxKuBNcBPRsSHgQciYl1mfjsi1gFHu1FQSVLrOu5zz8xtmXl2Zk4AlwGfy8zfBXYBm+u7bQZuqVxKSVJbejHO/VrglRFxF/DK+vJQWjxbZEmzR0oqW1c6bjNzDpirf/4ecFE3zitJ6oxPqEpSgcoJ9/eeVvuzQjj2XdIglRPukqQnGe5dcrInVX2KVVK/Ge6SVCDDfQkOe5Q0zAx3SSqQ4S5JBSr6NXsHztsIsOykYq1otZvGG6eSVgJb7pJUoKJb7gsmd0yys8NjB3ljtfEF2hPLTAMsSY1GItyHTWOoS1In7JaRpAIZ7pJUIMNdkgo0EuG+Up42dZikpH7xhuqIm1jiRdiShttItNwladQY7hWslO4eSVrMcJekAtnn3iFb7Sdaqu/+0LWX9LkkksCWe9taCfVW9nHkjKReMtwlqUDFhPuB2ecOugh9M/m0g84/I+mkigl3SdKPdXxDNSKeB/w9cBbwBLA9Mz8QEWcAHwcmgEPApZn5YPWitmfhRR3qLh96koZDlZb7ceCqzNwIvBR4a0ScD2wFdmfmucDu+nKxdr7v+JM3UB1BI2ml6DjcM/Pbmfmf9c//CxwA1gObgB313XYAr61aSElSeyIzq58kYgK4HbgAOJyZaxu2PZiZpzc5ZguwBWB8fPzC2dnZjq49Pz/PqnvvBWDNGT+CdT/Po/v3d3SuVt1zVvD87zT/e1vYds9ZwfnHjgFw5+rVT25vXLfwuRX7ntjw5OcNpz7G2GP3n7BupZpcf1pXzjM/P8/Y2FhXzjUsrPNoqFLnmZmZvZk51Wxb5XCPiDHgX4FrMvPmiHiolXBvNDU1lXv27Ono+nNzc4y/5fcB2HjZ/fDeh3ve337ptlVLdsEsbLt02yr2HTwMnDimvXHdwudWNL5m78YX3cX0N64eilfvdeshprm5Oaanp7tyrmFhnUdDlTpHxJLhXukJ1Yh4OvAJ4COZeXN99QMRsS4zvx0R64CjVa6xEq2UvnXfsSppKVVGywRwPXAgM9/fsGkXsBm4tv7zlkolHGIHZp/LpdtO/CtubMUvfG6nBV+Kk426ccoCqboqLfeXA28A9kXEV+rr3kMt1HdGxJXAYeD11YooSWpXx+Gemf8OxBKbL+r0vJKk6nxCVZIKVNyUvxNbP8Wtgy5EDzTePJ3jT0+6fdhvrjbrj79q8jjT/S+KNLRsuUtSgYoK95UwR3onwyQnN5zTs7IfWnO5M0hKI6i4bplb/+ldgy6CGjjRmDQYRbXcJUk1xbXc1VxJN1wX8/2t0lPZcpekAhUV7itlzhdJGjS7ZXpk8RfNwkyR/dDJ6JgSu23avZm7VDeO3T4aRkW13CVJNbbcV5DFY91HcbbIYWKLXiuZLXdJKpAt9xE0rP3rPhAltc5w77EqI3gG9TKPYQ3/qrr15WF3jVYCu2UkqUC23PtooRXfzSGR7b5oezEnFeufdv9ncOPFz+z5tR3+WS5b7pJUIMN9QKpMDXzn6tU9KFFzzaYMXlhnq19aueyWkUZYu11FJXfXlFY3W+6SVCBb7gOw0CXTi/lmBjV8cimjOqyyG/bd9zBvatKaPFlLctSeBSittd1NttwlqUC23FeYxtb8wudO3q/arAXfbNhk1aGUvbLQ4l+qtb/c9pKtxNZ5t4ZaXjV5vO3/rbRz/lFiuA+pxsBfLvy78fLtbo6M6bSrxtE5o8uwbl/PumUi4uKI+EZE3B0RW3t1HUnSU/Wk5R4RpwB/C7wSOAL8R0Tsysw7e3G9Ydb41GqzG61Vn2pdqtXejdY8NG+FL9XCbra+VzdcvZG7Mtji/rGl/i66+SRyo1613F8C3J2Z92TmMWAW2NSja0mSFonM7P5JI14HXJyZv1dffgPwi5n5toZ9tgBb6osvAL7R4eXOBL5bobjDyDqPBus8GqrU+acz89nNNvTqhmo0WXfCt0hmbge2V75QxJ7MnKp6nmFinUeDdR4Nvapzr7pljgDPa1g+G7i/R9eSJC3Sq3D/D+DciNgQEauBy4BdPbqWJGmRnnTLZObxiHgb8BngFOCGzNzfi2vRha6dIWSdR4N1Hg09qXNPbqhKkgbLuWUkqUCGuyQVaCjCfbmpDKLmr+vbvxoRLx5EObuphTpfUa/rVyPi8xHxokGUs5tanbIiIn4hIh6vP08x1Fqpc0RMR8RXImJ/RPxrv8vYCy38+z4tIv45Iv6rXu83D6Kc3RIRN0TE0Yj42hLbu59hmbmi/1C7IfvfwPOB1cB/Aecv2ufVwK3Uxte/FPjioMvdhzq/DDi9/vlVo1Dnhv0+B/wL8LpBl7sPv+e1wJ3AOfXl5wy63H2q93uAP69/fjbwfWD1oMteoc6vAF4MfG2J7V3PsGFoubcylcEm4O+z5g5gbUSs63dBu2jZOmfm5zPzwfriHdSeJRhmrU5Z8XbgE8DRfhauR1qp8+XAzZl5GCAzR6XeCTwrIgIYoxbu7b94eIXIzNup1WEpXc+wYQj39cC3GpaP1Ne1u88wabc+V1L71h9my9Y5ItYDvwX8XR/L1Uut/J5/Fjg9IuYiYm9EvLFvpeudVup9HbCR2sOP+4B3ZuYT/SneQHQ9w4ZhPvdlpzJocZ9h0nJ9ImKGWrj/ck9L1Hut1PmvgHdn5uO1Bt3Qa6XOq4ALgYuAU4EvRMQdmfnNXheuh1qp928AXwF+DfgZ4LaI+LfM/EGvCzcgXc+wYQj3VqYyKG26g5bqExE/B3wIeFVmfq9PZeuVVuo8BczWg/1M4NURcTwz/6k/Rey6Vv9tfzczHwEeiYjbgRcBwxzurdT7zcC1WeuQvjsiDgLnAV/qTxH7rusZNgzdMq1MZbALeGP9jvNLgYcz89v9LmgXLVvniDgHuBl4w5C34hYsW+fM3JCZE5k5AdwE/MEQBzu09m/7FuBXImJVRDwD+EXgQJ/L2W2t1Pswtf+tEBHj1GaOvaevpeyvrmfYim+55xJTGUTEW+rb/47ayIlXA3cD/0ftW39otVjnPwF+CvhgvSV7PId4Nr0W61yUVuqcmQci4tPAV4EngA9lZtPhdMOixd/1nwE3RsQ+al0W787MoZ0KOCI+BkwDZ0bEEeBq4OnQuwxz+gFJKtAwdMtIktpkuEtSgQx3SSqQ4S5JBTLcJalAhrskFchwl6QC/T/9XOombTwIxwAAAABJRU5ErkJggg==",
      "text/plain": [
       "<Figure size 432x288 with 1 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "# tot number of queries (denom if CTR)\n",
    "denominator = query_events.groupby('query').count().rename(columns={'query_id': 'tot_query_count'})\n",
    "\n",
    "# click counts per doc in query\n",
    "click_thru_rate = clicks_per_query.groupby(['query', 'clicked_doc_id']).count().rename(columns={'query_id':'click_count'}).reset_index()\n",
    "click_thru_rate = click_thru_rate.merge(denominator, on='query', how='left')\n",
    "\n",
    "click_thru_rate['ctr'] = click_thru_rate['click_count'] / click_thru_rate['tot_query_count']\n",
    "click_thru_rate = click_thru_rate.sort_values(['query', 'ctr'], ascending=[True, False])\n",
    "\n",
    "# Get rid of doc_id 0, which is all the queries with no clicks\n",
    "# We treat these as a canonical ranking from the source system, assume it's relatively\n",
    "# highly tuned and the source CTRs are pretty reasonably close to actual relevance ranking\n",
    "# in the source system. Of course this is a dubious assumption in a real search system, \n",
    "# but for our purposes - to synthesize reasonable looking search sessions - it will serve\n",
    "canonical_rankings = click_thru_rate[click_thru_rate['clicked_doc_id'] != 0].reset_index()\n",
    "\n",
    "\n",
    "# Just for display and sanity checking\n",
    "print(canonical_rankings.groupby('query').nth(0)['ctr'].median())\n",
    "canonical_rankings.groupby('query').nth(0)['ctr'].hist(bins=50)\n",
    "\n",
    "print(canonical_rankings.groupby('query').nth(1)['ctr'].median())\n",
    "canonical_rankings.groupby('query').nth(1)['ctr'].hist(bins=50)\n",
    "\n",
    "print(canonical_rankings.groupby('query').nth(2)['ctr'].median())\n",
    "canonical_rankings.groupby('query').nth(2)['ctr'].hist(bins=50)\n",
    "\n",
    "print(canonical_rankings.groupby('query').nth(3)['ctr'].median())\n",
    "canonical_rankings.groupby('query').nth(3)['ctr'].hist(bins=50)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Compute stats of each CTR position\n",
    "\n",
    "What's the mean, median, std deviaton, etc of CTR in each position? This lets us synthesize sessions assuming the above average or below average CTR is an indication of above or below average relevance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 179,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>query</th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>click_count</th>\n",
       "      <th>tot_query_count</th>\n",
       "      <th>ctr</th>\n",
       "      <th>rank</th>\n",
       "      <th>posn_ctr_mean</th>\n",
       "      <th>posn_ctr_std</th>\n",
       "      <th>posn_ctr_median</th>\n",
       "      <th>posn_ctr_mad</th>\n",
       "      <th>ctr_std_z_score</th>\n",
       "      <th>ctr_mod_z_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15580</th>\n",
       "      <td>15844</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505451713</td>\n",
       "      <td>20</td>\n",
       "      <td>246</td>\n",
       "      <td>0.081301</td>\n",
       "      <td>0</td>\n",
       "      <td>0.215521</td>\n",
       "      <td>0.172469</td>\n",
       "      <td>0.158897</td>\n",
       "      <td>0.124872</td>\n",
       "      <td>-0.778227</td>\n",
       "      <td>-0.621404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15581</th>\n",
       "      <td>15893</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883929085118</td>\n",
       "      <td>18</td>\n",
       "      <td>246</td>\n",
       "      <td>0.073171</td>\n",
       "      <td>1</td>\n",
       "      <td>0.121488</td>\n",
       "      <td>0.070496</td>\n",
       "      <td>0.109649</td>\n",
       "      <td>0.054231</td>\n",
       "      <td>-0.685393</td>\n",
       "      <td>-0.672647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15582</th>\n",
       "      <td>15887</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883049066905</td>\n",
       "      <td>16</td>\n",
       "      <td>246</td>\n",
       "      <td>0.065041</td>\n",
       "      <td>2</td>\n",
       "      <td>0.087093</td>\n",
       "      <td>0.048757</td>\n",
       "      <td>0.078425</td>\n",
       "      <td>0.038821</td>\n",
       "      <td>-0.452290</td>\n",
       "      <td>-0.344763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15583</th>\n",
       "      <td>15854</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36172950027</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>3</td>\n",
       "      <td>0.065306</td>\n",
       "      <td>0.036182</td>\n",
       "      <td>0.059932</td>\n",
       "      <td>0.028924</td>\n",
       "      <td>-0.344385</td>\n",
       "      <td>-0.244987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15584</th>\n",
       "      <td>15870</td>\n",
       "      <td>dryer</td>\n",
       "      <td>74108056764</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>4</td>\n",
       "      <td>0.051849</td>\n",
       "      <td>0.030129</td>\n",
       "      <td>0.048343</td>\n",
       "      <td>0.024117</td>\n",
       "      <td>0.033090</td>\n",
       "      <td>0.186711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15585</th>\n",
       "      <td>15872</td>\n",
       "      <td>dryer</td>\n",
       "      <td>77283045400</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>5</td>\n",
       "      <td>0.041020</td>\n",
       "      <td>0.024764</td>\n",
       "      <td>0.039578</td>\n",
       "      <td>0.019993</td>\n",
       "      <td>0.477524</td>\n",
       "      <td>0.663618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15586</th>\n",
       "      <td>15883</td>\n",
       "      <td>dryer</td>\n",
       "      <td>783722274422</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>6</td>\n",
       "      <td>0.031860</td>\n",
       "      <td>0.019346</td>\n",
       "      <td>0.031660</td>\n",
       "      <td>0.015932</td>\n",
       "      <td>1.084741</td>\n",
       "      <td>1.329765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15587</th>\n",
       "      <td>15880</td>\n",
       "      <td>dryer</td>\n",
       "      <td>665331101927</td>\n",
       "      <td>11</td>\n",
       "      <td>246</td>\n",
       "      <td>0.044715</td>\n",
       "      <td>7</td>\n",
       "      <td>0.026049</td>\n",
       "      <td>0.016632</td>\n",
       "      <td>0.025751</td>\n",
       "      <td>0.013968</td>\n",
       "      <td>1.122345</td>\n",
       "      <td>1.357717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15588</th>\n",
       "      <td>15848</td>\n",
       "      <td>dryer</td>\n",
       "      <td>14381196320</td>\n",
       "      <td>9</td>\n",
       "      <td>246</td>\n",
       "      <td>0.036585</td>\n",
       "      <td>8</td>\n",
       "      <td>0.021772</td>\n",
       "      <td>0.014593</td>\n",
       "      <td>0.020859</td>\n",
       "      <td>0.012446</td>\n",
       "      <td>1.015130</td>\n",
       "      <td>1.263529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15589</th>\n",
       "      <td>15871</td>\n",
       "      <td>dryer</td>\n",
       "      <td>74108096487</td>\n",
       "      <td>9</td>\n",
       "      <td>246</td>\n",
       "      <td>0.036585</td>\n",
       "      <td>9</td>\n",
       "      <td>0.018520</td>\n",
       "      <td>0.012575</td>\n",
       "      <td>0.017204</td>\n",
       "      <td>0.010934</td>\n",
       "      <td>1.436546</td>\n",
       "      <td>1.772473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15590</th>\n",
       "      <td>15886</td>\n",
       "      <td>dryer</td>\n",
       "      <td>856751002097</td>\n",
       "      <td>9</td>\n",
       "      <td>246</td>\n",
       "      <td>0.036585</td>\n",
       "      <td>10</td>\n",
       "      <td>0.016110</td>\n",
       "      <td>0.011305</td>\n",
       "      <td>0.013758</td>\n",
       "      <td>0.009929</td>\n",
       "      <td>1.811274</td>\n",
       "      <td>2.299050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15591</th>\n",
       "      <td>15846</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505525766</td>\n",
       "      <td>8</td>\n",
       "      <td>246</td>\n",
       "      <td>0.032520</td>\n",
       "      <td>11</td>\n",
       "      <td>0.014300</td>\n",
       "      <td>0.010128</td>\n",
       "      <td>0.011174</td>\n",
       "      <td>0.008936</td>\n",
       "      <td>1.799011</td>\n",
       "      <td>2.388945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15592</th>\n",
       "      <td>15900</td>\n",
       "      <td>dryer</td>\n",
       "      <td>048231011402</td>\n",
       "      <td>8</td>\n",
       "      <td>246</td>\n",
       "      <td>0.032520</td>\n",
       "      <td>12</td>\n",
       "      <td>0.012683</td>\n",
       "      <td>0.009012</td>\n",
       "      <td>0.009276</td>\n",
       "      <td>0.007897</td>\n",
       "      <td>2.201221</td>\n",
       "      <td>2.943346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15593</th>\n",
       "      <td>15901</td>\n",
       "      <td>dryer</td>\n",
       "      <td>074108007469</td>\n",
       "      <td>7</td>\n",
       "      <td>246</td>\n",
       "      <td>0.028455</td>\n",
       "      <td>13</td>\n",
       "      <td>0.011451</td>\n",
       "      <td>0.008090</td>\n",
       "      <td>0.008439</td>\n",
       "      <td>0.007052</td>\n",
       "      <td>2.101933</td>\n",
       "      <td>2.838598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15594</th>\n",
       "      <td>15865</td>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011396</td>\n",
       "      <td>6</td>\n",
       "      <td>246</td>\n",
       "      <td>0.024390</td>\n",
       "      <td>14</td>\n",
       "      <td>0.010445</td>\n",
       "      <td>0.007291</td>\n",
       "      <td>0.007313</td>\n",
       "      <td>0.006331</td>\n",
       "      <td>1.912821</td>\n",
       "      <td>2.697482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15595</th>\n",
       "      <td>15857</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725561977</td>\n",
       "      <td>4</td>\n",
       "      <td>246</td>\n",
       "      <td>0.016260</td>\n",
       "      <td>15</td>\n",
       "      <td>0.009547</td>\n",
       "      <td>0.006554</td>\n",
       "      <td>0.006536</td>\n",
       "      <td>0.005619</td>\n",
       "      <td>1.024235</td>\n",
       "      <td>1.730444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15596</th>\n",
       "      <td>15860</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725578241</td>\n",
       "      <td>4</td>\n",
       "      <td>246</td>\n",
       "      <td>0.016260</td>\n",
       "      <td>16</td>\n",
       "      <td>0.008799</td>\n",
       "      <td>0.005915</td>\n",
       "      <td>0.005435</td>\n",
       "      <td>0.005005</td>\n",
       "      <td>1.261551</td>\n",
       "      <td>2.162826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15597</th>\n",
       "      <td>15847</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505527456</td>\n",
       "      <td>3</td>\n",
       "      <td>246</td>\n",
       "      <td>0.012195</td>\n",
       "      <td>17</td>\n",
       "      <td>0.008103</td>\n",
       "      <td>0.005321</td>\n",
       "      <td>0.005128</td>\n",
       "      <td>0.004453</td>\n",
       "      <td>0.769038</td>\n",
       "      <td>1.586958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15598</th>\n",
       "      <td>15874</td>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226703</td>\n",
       "      <td>3</td>\n",
       "      <td>246</td>\n",
       "      <td>0.012195</td>\n",
       "      <td>18</td>\n",
       "      <td>0.007613</td>\n",
       "      <td>0.004868</td>\n",
       "      <td>0.005051</td>\n",
       "      <td>0.004017</td>\n",
       "      <td>0.941312</td>\n",
       "      <td>1.778745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15599</th>\n",
       "      <td>15875</td>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226727</td>\n",
       "      <td>3</td>\n",
       "      <td>246</td>\n",
       "      <td>0.012195</td>\n",
       "      <td>19</td>\n",
       "      <td>0.007177</td>\n",
       "      <td>0.004475</td>\n",
       "      <td>0.004902</td>\n",
       "      <td>0.003650</td>\n",
       "      <td>1.121460</td>\n",
       "      <td>1.997980</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       index  query clicked_doc_id  click_count  tot_query_count       ctr  \\\n",
       "15580  15844  dryer    12505451713           20              246  0.081301   \n",
       "15581  15893  dryer   883929085118           18              246  0.073171   \n",
       "15582  15887  dryer   883049066905           16              246  0.065041   \n",
       "15583  15854  dryer    36172950027           13              246  0.052846   \n",
       "15584  15870  dryer    74108056764           13              246  0.052846   \n",
       "15585  15872  dryer    77283045400           13              246  0.052846   \n",
       "15586  15883  dryer   783722274422           13              246  0.052846   \n",
       "15587  15880  dryer   665331101927           11              246  0.044715   \n",
       "15588  15848  dryer    14381196320            9              246  0.036585   \n",
       "15589  15871  dryer    74108096487            9              246  0.036585   \n",
       "15590  15886  dryer   856751002097            9              246  0.036585   \n",
       "15591  15846  dryer    12505525766            8              246  0.032520   \n",
       "15592  15900  dryer   048231011402            8              246  0.032520   \n",
       "15593  15901  dryer   074108007469            7              246  0.028455   \n",
       "15594  15865  dryer    48231011396            6              246  0.024390   \n",
       "15595  15857  dryer    36725561977            4              246  0.016260   \n",
       "15596  15860  dryer    36725578241            4              246  0.016260   \n",
       "15597  15847  dryer    12505527456            3              246  0.012195   \n",
       "15598  15874  dryer    84691226703            3              246  0.012195   \n",
       "15599  15875  dryer    84691226727            3              246  0.012195   \n",
       "\n",
       "       rank  posn_ctr_mean  posn_ctr_std  posn_ctr_median  posn_ctr_mad  \\\n",
       "15580     0       0.215521      0.172469         0.158897      0.124872   \n",
       "15581     1       0.121488      0.070496         0.109649      0.054231   \n",
       "15582     2       0.087093      0.048757         0.078425      0.038821   \n",
       "15583     3       0.065306      0.036182         0.059932      0.028924   \n",
       "15584     4       0.051849      0.030129         0.048343      0.024117   \n",
       "15585     5       0.041020      0.024764         0.039578      0.019993   \n",
       "15586     6       0.031860      0.019346         0.031660      0.015932   \n",
       "15587     7       0.026049      0.016632         0.025751      0.013968   \n",
       "15588     8       0.021772      0.014593         0.020859      0.012446   \n",
       "15589     9       0.018520      0.012575         0.017204      0.010934   \n",
       "15590    10       0.016110      0.011305         0.013758      0.009929   \n",
       "15591    11       0.014300      0.010128         0.011174      0.008936   \n",
       "15592    12       0.012683      0.009012         0.009276      0.007897   \n",
       "15593    13       0.011451      0.008090         0.008439      0.007052   \n",
       "15594    14       0.010445      0.007291         0.007313      0.006331   \n",
       "15595    15       0.009547      0.006554         0.006536      0.005619   \n",
       "15596    16       0.008799      0.005915         0.005435      0.005005   \n",
       "15597    17       0.008103      0.005321         0.005128      0.004453   \n",
       "15598    18       0.007613      0.004868         0.005051      0.004017   \n",
       "15599    19       0.007177      0.004475         0.004902      0.003650   \n",
       "\n",
       "       ctr_std_z_score  ctr_mod_z_score  \n",
       "15580        -0.778227        -0.621404  \n",
       "15581        -0.685393        -0.672647  \n",
       "15582        -0.452290        -0.344763  \n",
       "15583        -0.344385        -0.244987  \n",
       "15584         0.033090         0.186711  \n",
       "15585         0.477524         0.663618  \n",
       "15586         1.084741         1.329765  \n",
       "15587         1.122345         1.357717  \n",
       "15588         1.015130         1.263529  \n",
       "15589         1.436546         1.772473  \n",
       "15590         1.811274         2.299050  \n",
       "15591         1.799011         2.388945  \n",
       "15592         2.201221         2.943346  \n",
       "15593         2.101933         2.838598  \n",
       "15594         1.912821         2.697482  \n",
       "15595         1.024235         1.730444  \n",
       "15596         1.261551         2.162826  \n",
       "15597         0.769038         1.586958  \n",
       "15598         0.941312         1.778745  \n",
       "15599         1.121460         1.997980  "
      ]
     },
     "execution_count": 179,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "canonical_rankings['rank'] = canonical_rankings.groupby('query').cumcount()\n",
    "\n",
    "# Idea\n",
    "# Compute neg z scores for items below mean and positive z scores for items above mean\n",
    "# but negative z scores are fit to a distribution of only the negative values and the negative values + mean\n",
    "# similarly for positive values\n",
    "\n",
    "max_depth = canonical_rankings['rank'].max()\n",
    "for i in range(0, max_depth):\n",
    "    idxs = canonical_rankings[canonical_rankings['rank'] == i].index\n",
    "   \n",
    "    # Mean based statistics\n",
    "    canonical_rankings.loc[idxs, 'posn_ctr_mean'] = canonical_rankings[canonical_rankings['rank'] == i]['ctr'].mean()\n",
    "    canonical_rankings.loc[idxs, 'posn_ctr_std'] = canonical_rankings[canonical_rankings['rank'] == i]['ctr'].std()\n",
    " \n",
    "    # Median based statistics (less outlier prone)\n",
    "    canonical_rankings.loc[idxs, 'posn_ctr_median'] = canonical_rankings[canonical_rankings['rank'] == i]['ctr'].median()\n",
    "    canonical_rankings.loc[idxs, 'posn_ctr_mad'] = canonical_rankings[canonical_rankings['rank'] == i]['ctr'].mad()\n",
    "\n",
    "canonical_rankings['ctr_std_z_score'] = (canonical_rankings['ctr'] - canonical_rankings['posn_ctr_mean']) / canonical_rankings['posn_ctr_std']\n",
    "canonical_rankings['ctr_mod_z_score'] = (canonical_rankings['ctr'] - canonical_rankings['posn_ctr_median']) / canonical_rankings['posn_ctr_mad']\n",
    "\n",
    "orig_dryer = canonical_rankings[canonical_rankings['query'] == 'dryer'].head(20)\n",
    "orig_dryer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Create a single search session\n",
    "\n",
    "Given statistics on CTR, assume the search engine returns a shuffled list of results, and recompute likely CTR for each position. Then randomly select whether there was a click"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 180,
   "metadata": {},
   "outputs": [],
   "source": [
    "def dest_rankings(n, query, baselines):\n",
    "    if query not in baselines:\n",
    "        shuffled = canonical_rankings[canonical_rankings['query'] == query]\n",
    "        shuffled = shuffled[['posn_ctr_mean', 'posn_ctr_std', 'rank', 'posn_ctr_mad', 'posn_ctr_median']]\\\n",
    "                    .rename(columns={'rank': 'dest_rank'})\n",
    "        shuffled = shuffled[shuffled['dest_rank'] < n].sample(frac=1)\n",
    "        baselines[query] = shuffled    \n",
    "    return baselines[query]\n",
    "\n",
    "def slightly_random_ranking(n, query, baselines):\n",
    "    top_n = dest_rankings(n, query, baselines).copy()\n",
    "    to_swap = list(range(0,20))\n",
    "    random.shuffle(to_swap)\n",
    "    to_swap = to_swap[:8]\n",
    "    \n",
    "    for a, b in zip(to_swap, to_swap[1:]):\n",
    "        b_val = top_n.iloc[b]\n",
    "        a_val = top_n.iloc[a].copy()\n",
    "        top_n.iloc[a] = b_val\n",
    "        top_n.iloc[b] = a_val\n",
    "    \n",
    "    # Swap a few posns\n",
    "    return top_n\n",
    "\n",
    "def synthesize_session(query, sess_id, baselines):\n",
    "    posn_weights=[]\n",
    "    \n",
    "    canonical = canonical_rankings[canonical_rankings['query'] == query][canonical_rankings['rank'] < DOCS_PER_SESSION]\n",
    "    \n",
    "    top_n = slightly_random_ranking(DOCS_PER_SESSION, query, baselines)\n",
    "    \n",
    "    \n",
    "    \n",
    "    shuffled = top_n.rename(columns={'posn_ctr_std': 'dest_ctr_std',\n",
    "                         'posn_ctr_mean': 'dest_ctr_mean',\n",
    "                         'posn_ctr_mad': 'dest_ctr_mad',\n",
    "                         'posn_ctr_median': 'dest_ctr_median'})\n",
    "    shuffled = shuffled.reset_index(drop=True).reset_index().rename(columns={'index': 'rank'})\n",
    "    shuffled = shuffled.merge(canonical, on='rank', how='left')\n",
    "    shuffled['dest_ctr_median_based'] = (shuffled['ctr_mod_z_score'] * shuffled['dest_ctr_mad']) + (shuffled['dest_ctr_median'])\n",
    "    shuffled['dest_ctr_mean_based'] = (shuffled['ctr_std_z_score'] * shuffled['dest_ctr_std']) + (shuffled['dest_ctr_mean'])\n",
    "    shuffled['draw'] = numpy.random.rand(len(shuffled))\n",
    "    shuffled['clicked'] = shuffled['draw'] < shuffled['dest_ctr_mean_based']\n",
    "    shuffled['sess_id'] = sess_id\n",
    "\n",
    "    return shuffled\n",
    "\n",
    "baselines={}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 181,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:28: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>dest_ctr_mean</th>\n",
       "      <th>dest_ctr_std</th>\n",
       "      <th>dest_rank</th>\n",
       "      <th>dest_ctr_mad</th>\n",
       "      <th>dest_ctr_median</th>\n",
       "      <th>index</th>\n",
       "      <th>query</th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>click_count</th>\n",
       "      <th>...</th>\n",
       "      <th>posn_ctr_std</th>\n",
       "      <th>posn_ctr_median</th>\n",
       "      <th>posn_ctr_mad</th>\n",
       "      <th>ctr_std_z_score</th>\n",
       "      <th>ctr_mod_z_score</th>\n",
       "      <th>dest_ctr_median_based</th>\n",
       "      <th>dest_ctr_mean_based</th>\n",
       "      <th>draw</th>\n",
       "      <th>clicked</th>\n",
       "      <th>sess_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0.005123</td>\n",
       "      <td>0.002433</td>\n",
       "      <td>27.0</td>\n",
       "      <td>0.001776</td>\n",
       "      <td>0.004444</td>\n",
       "      <td>15844</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505451713</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0.172469</td>\n",
       "      <td>0.158897</td>\n",
       "      <td>0.124872</td>\n",
       "      <td>-0.778227</td>\n",
       "      <td>-0.621404</td>\n",
       "      <td>0.003341</td>\n",
       "      <td>0.003229</td>\n",
       "      <td>0.664851</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>0.006111</td>\n",
       "      <td>0.003424</td>\n",
       "      <td>22.0</td>\n",
       "      <td>0.002703</td>\n",
       "      <td>0.004651</td>\n",
       "      <td>15893</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883929085118</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>0.070496</td>\n",
       "      <td>0.109649</td>\n",
       "      <td>0.054231</td>\n",
       "      <td>-0.685393</td>\n",
       "      <td>-0.672647</td>\n",
       "      <td>0.002833</td>\n",
       "      <td>0.003764</td>\n",
       "      <td>0.100723</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>0.031860</td>\n",
       "      <td>0.019346</td>\n",
       "      <td>6.0</td>\n",
       "      <td>0.015932</td>\n",
       "      <td>0.031660</td>\n",
       "      <td>15887</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883049066905</td>\n",
       "      <td>16</td>\n",
       "      <td>...</td>\n",
       "      <td>0.048757</td>\n",
       "      <td>0.078425</td>\n",
       "      <td>0.038821</td>\n",
       "      <td>-0.452290</td>\n",
       "      <td>-0.344763</td>\n",
       "      <td>0.026167</td>\n",
       "      <td>0.023110</td>\n",
       "      <td>0.749103</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>0.004872</td>\n",
       "      <td>0.002181</td>\n",
       "      <td>29.0</td>\n",
       "      <td>0.001557</td>\n",
       "      <td>0.004405</td>\n",
       "      <td>15854</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36172950027</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>0.036182</td>\n",
       "      <td>0.059932</td>\n",
       "      <td>0.028924</td>\n",
       "      <td>-0.344385</td>\n",
       "      <td>-0.244987</td>\n",
       "      <td>0.004024</td>\n",
       "      <td>0.004121</td>\n",
       "      <td>0.506594</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>0.008799</td>\n",
       "      <td>0.005915</td>\n",
       "      <td>16.0</td>\n",
       "      <td>0.005005</td>\n",
       "      <td>0.005435</td>\n",
       "      <td>15870</td>\n",
       "      <td>dryer</td>\n",
       "      <td>74108056764</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>0.030129</td>\n",
       "      <td>0.048343</td>\n",
       "      <td>0.024117</td>\n",
       "      <td>0.033090</td>\n",
       "      <td>0.186711</td>\n",
       "      <td>0.006369</td>\n",
       "      <td>0.008994</td>\n",
       "      <td>0.645359</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>0.011451</td>\n",
       "      <td>0.008090</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.007052</td>\n",
       "      <td>0.008439</td>\n",
       "      <td>15872</td>\n",
       "      <td>dryer</td>\n",
       "      <td>77283045400</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>0.024764</td>\n",
       "      <td>0.039578</td>\n",
       "      <td>0.019993</td>\n",
       "      <td>0.477524</td>\n",
       "      <td>0.663618</td>\n",
       "      <td>0.013118</td>\n",
       "      <td>0.015314</td>\n",
       "      <td>0.938898</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>0.004991</td>\n",
       "      <td>0.002298</td>\n",
       "      <td>28.0</td>\n",
       "      <td>0.001657</td>\n",
       "      <td>0.004425</td>\n",
       "      <td>15883</td>\n",
       "      <td>dryer</td>\n",
       "      <td>783722274422</td>\n",
       "      <td>13</td>\n",
       "      <td>...</td>\n",
       "      <td>0.019346</td>\n",
       "      <td>0.031660</td>\n",
       "      <td>0.015932</td>\n",
       "      <td>1.084741</td>\n",
       "      <td>1.329765</td>\n",
       "      <td>0.006629</td>\n",
       "      <td>0.007484</td>\n",
       "      <td>0.588894</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>0.009547</td>\n",
       "      <td>0.006554</td>\n",
       "      <td>15.0</td>\n",
       "      <td>0.005619</td>\n",
       "      <td>0.006536</td>\n",
       "      <td>15880</td>\n",
       "      <td>dryer</td>\n",
       "      <td>665331101927</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>0.016632</td>\n",
       "      <td>0.025751</td>\n",
       "      <td>0.013968</td>\n",
       "      <td>1.122345</td>\n",
       "      <td>1.357717</td>\n",
       "      <td>0.014166</td>\n",
       "      <td>0.016903</td>\n",
       "      <td>0.126852</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8</td>\n",
       "      <td>0.006741</td>\n",
       "      <td>0.004042</td>\n",
       "      <td>20.0</td>\n",
       "      <td>0.003251</td>\n",
       "      <td>0.004808</td>\n",
       "      <td>15848</td>\n",
       "      <td>dryer</td>\n",
       "      <td>14381196320</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.014593</td>\n",
       "      <td>0.020859</td>\n",
       "      <td>0.012446</td>\n",
       "      <td>1.015130</td>\n",
       "      <td>1.263529</td>\n",
       "      <td>0.008916</td>\n",
       "      <td>0.010844</td>\n",
       "      <td>0.622237</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9</td>\n",
       "      <td>0.012683</td>\n",
       "      <td>0.009012</td>\n",
       "      <td>12.0</td>\n",
       "      <td>0.007897</td>\n",
       "      <td>0.009276</td>\n",
       "      <td>15871</td>\n",
       "      <td>dryer</td>\n",
       "      <td>74108096487</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.012575</td>\n",
       "      <td>0.017204</td>\n",
       "      <td>0.010934</td>\n",
       "      <td>1.436546</td>\n",
       "      <td>1.772473</td>\n",
       "      <td>0.023274</td>\n",
       "      <td>0.025629</td>\n",
       "      <td>0.666952</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10</td>\n",
       "      <td>0.215521</td>\n",
       "      <td>0.172469</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.124872</td>\n",
       "      <td>0.158897</td>\n",
       "      <td>15886</td>\n",
       "      <td>dryer</td>\n",
       "      <td>856751002097</td>\n",
       "      <td>9</td>\n",
       "      <td>...</td>\n",
       "      <td>0.011305</td>\n",
       "      <td>0.013758</td>\n",
       "      <td>0.009929</td>\n",
       "      <td>1.811274</td>\n",
       "      <td>2.299050</td>\n",
       "      <td>0.445985</td>\n",
       "      <td>0.527910</td>\n",
       "      <td>0.187322</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11</td>\n",
       "      <td>0.005460</td>\n",
       "      <td>0.002820</td>\n",
       "      <td>25.0</td>\n",
       "      <td>0.002111</td>\n",
       "      <td>0.004505</td>\n",
       "      <td>15846</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505525766</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.010128</td>\n",
       "      <td>0.011174</td>\n",
       "      <td>0.008936</td>\n",
       "      <td>1.799011</td>\n",
       "      <td>2.388945</td>\n",
       "      <td>0.009548</td>\n",
       "      <td>0.010532</td>\n",
       "      <td>0.599752</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12</td>\n",
       "      <td>0.006423</td>\n",
       "      <td>0.003708</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.002963</td>\n",
       "      <td>0.004717</td>\n",
       "      <td>15900</td>\n",
       "      <td>dryer</td>\n",
       "      <td>048231011402</td>\n",
       "      <td>8</td>\n",
       "      <td>...</td>\n",
       "      <td>0.009012</td>\n",
       "      <td>0.009276</td>\n",
       "      <td>0.007897</td>\n",
       "      <td>2.201221</td>\n",
       "      <td>2.943346</td>\n",
       "      <td>0.013437</td>\n",
       "      <td>0.014585</td>\n",
       "      <td>0.074374</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13</td>\n",
       "      <td>0.065306</td>\n",
       "      <td>0.036182</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.028924</td>\n",
       "      <td>0.059932</td>\n",
       "      <td>15901</td>\n",
       "      <td>dryer</td>\n",
       "      <td>074108007469</td>\n",
       "      <td>7</td>\n",
       "      <td>...</td>\n",
       "      <td>0.008090</td>\n",
       "      <td>0.008439</td>\n",
       "      <td>0.007052</td>\n",
       "      <td>2.101933</td>\n",
       "      <td>2.838598</td>\n",
       "      <td>0.142035</td>\n",
       "      <td>0.141358</td>\n",
       "      <td>0.267837</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14</td>\n",
       "      <td>0.041020</td>\n",
       "      <td>0.024764</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.019993</td>\n",
       "      <td>0.039578</td>\n",
       "      <td>15865</td>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011396</td>\n",
       "      <td>6</td>\n",
       "      <td>...</td>\n",
       "      <td>0.007291</td>\n",
       "      <td>0.007313</td>\n",
       "      <td>0.006331</td>\n",
       "      <td>1.912821</td>\n",
       "      <td>2.697482</td>\n",
       "      <td>0.093509</td>\n",
       "      <td>0.088389</td>\n",
       "      <td>0.372377</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15</td>\n",
       "      <td>0.005897</td>\n",
       "      <td>0.003219</td>\n",
       "      <td>23.0</td>\n",
       "      <td>0.002507</td>\n",
       "      <td>0.004608</td>\n",
       "      <td>15857</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725561977</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.006554</td>\n",
       "      <td>0.006536</td>\n",
       "      <td>0.005619</td>\n",
       "      <td>1.024235</td>\n",
       "      <td>1.730444</td>\n",
       "      <td>0.008947</td>\n",
       "      <td>0.009195</td>\n",
       "      <td>0.636790</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16</td>\n",
       "      <td>0.018520</td>\n",
       "      <td>0.012575</td>\n",
       "      <td>9.0</td>\n",
       "      <td>0.010934</td>\n",
       "      <td>0.017204</td>\n",
       "      <td>15860</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725578241</td>\n",
       "      <td>4</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005915</td>\n",
       "      <td>0.005435</td>\n",
       "      <td>0.005005</td>\n",
       "      <td>1.261551</td>\n",
       "      <td>2.162826</td>\n",
       "      <td>0.040854</td>\n",
       "      <td>0.034385</td>\n",
       "      <td>0.566257</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17</td>\n",
       "      <td>0.005286</td>\n",
       "      <td>0.002583</td>\n",
       "      <td>26.0</td>\n",
       "      <td>0.001923</td>\n",
       "      <td>0.004484</td>\n",
       "      <td>15847</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505527456</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0.005321</td>\n",
       "      <td>0.005128</td>\n",
       "      <td>0.004453</td>\n",
       "      <td>0.769038</td>\n",
       "      <td>1.586958</td>\n",
       "      <td>0.007536</td>\n",
       "      <td>0.007273</td>\n",
       "      <td>0.386841</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18</td>\n",
       "      <td>0.121488</td>\n",
       "      <td>0.070496</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.054231</td>\n",
       "      <td>0.109649</td>\n",
       "      <td>15874</td>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226703</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0.004868</td>\n",
       "      <td>0.005051</td>\n",
       "      <td>0.004017</td>\n",
       "      <td>0.941312</td>\n",
       "      <td>1.778745</td>\n",
       "      <td>0.206112</td>\n",
       "      <td>0.187846</td>\n",
       "      <td>0.241156</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19</td>\n",
       "      <td>0.026049</td>\n",
       "      <td>0.016632</td>\n",
       "      <td>7.0</td>\n",
       "      <td>0.013968</td>\n",
       "      <td>0.025751</td>\n",
       "      <td>15875</td>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226727</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0.004475</td>\n",
       "      <td>0.004902</td>\n",
       "      <td>0.003650</td>\n",
       "      <td>1.121460</td>\n",
       "      <td>1.997980</td>\n",
       "      <td>0.053659</td>\n",
       "      <td>0.044701</td>\n",
       "      <td>0.480969</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20</td>\n",
       "      <td>0.021772</td>\n",
       "      <td>0.014593</td>\n",
       "      <td>8.0</td>\n",
       "      <td>0.012446</td>\n",
       "      <td>0.020859</td>\n",
       "      <td>15876</td>\n",
       "      <td>dryer</td>\n",
       "      <td>85391137726</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>0.004042</td>\n",
       "      <td>0.004808</td>\n",
       "      <td>0.003251</td>\n",
       "      <td>1.349483</td>\n",
       "      <td>2.272160</td>\n",
       "      <td>0.049139</td>\n",
       "      <td>0.041464</td>\n",
       "      <td>0.987882</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21</td>\n",
       "      <td>0.010445</td>\n",
       "      <td>0.007291</td>\n",
       "      <td>14.0</td>\n",
       "      <td>0.006331</td>\n",
       "      <td>0.007313</td>\n",
       "      <td>15856</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725560789</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.003708</td>\n",
       "      <td>0.004717</td>\n",
       "      <td>0.002963</td>\n",
       "      <td>0.460480</td>\n",
       "      <td>1.152068</td>\n",
       "      <td>0.014606</td>\n",
       "      <td>0.013802</td>\n",
       "      <td>0.239419</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22</td>\n",
       "      <td>0.008103</td>\n",
       "      <td>0.005321</td>\n",
       "      <td>17.0</td>\n",
       "      <td>0.004453</td>\n",
       "      <td>0.005128</td>\n",
       "      <td>15859</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725578142</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.003424</td>\n",
       "      <td>0.004651</td>\n",
       "      <td>0.002703</td>\n",
       "      <td>0.589684</td>\n",
       "      <td>1.287277</td>\n",
       "      <td>0.010861</td>\n",
       "      <td>0.011241</td>\n",
       "      <td>0.738529</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23</td>\n",
       "      <td>0.051849</td>\n",
       "      <td>0.030129</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.024117</td>\n",
       "      <td>0.048343</td>\n",
       "      <td>15864</td>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011198</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.003219</td>\n",
       "      <td>0.004608</td>\n",
       "      <td>0.002507</td>\n",
       "      <td>0.693542</td>\n",
       "      <td>1.404657</td>\n",
       "      <td>0.082219</td>\n",
       "      <td>0.072744</td>\n",
       "      <td>0.577586</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24</td>\n",
       "      <td>0.016110</td>\n",
       "      <td>0.011305</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.009929</td>\n",
       "      <td>0.013758</td>\n",
       "      <td>15881</td>\n",
       "      <td>dryer</td>\n",
       "      <td>723755811416</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.003001</td>\n",
       "      <td>0.004566</td>\n",
       "      <td>0.002306</td>\n",
       "      <td>0.816731</td>\n",
       "      <td>1.545758</td>\n",
       "      <td>0.029106</td>\n",
       "      <td>0.025342</td>\n",
       "      <td>0.817041</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25</td>\n",
       "      <td>0.007177</td>\n",
       "      <td>0.004475</td>\n",
       "      <td>19.0</td>\n",
       "      <td>0.003650</td>\n",
       "      <td>0.004902</td>\n",
       "      <td>15891</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883049199061</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002820</td>\n",
       "      <td>0.004505</td>\n",
       "      <td>0.002111</td>\n",
       "      <td>0.946966</td>\n",
       "      <td>1.717253</td>\n",
       "      <td>0.011170</td>\n",
       "      <td>0.011414</td>\n",
       "      <td>0.739095</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26</td>\n",
       "      <td>0.014300</td>\n",
       "      <td>0.010128</td>\n",
       "      <td>11.0</td>\n",
       "      <td>0.008936</td>\n",
       "      <td>0.011174</td>\n",
       "      <td>15904</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883049200057</td>\n",
       "      <td>2</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002583</td>\n",
       "      <td>0.004484</td>\n",
       "      <td>0.001923</td>\n",
       "      <td>1.100956</td>\n",
       "      <td>1.895937</td>\n",
       "      <td>0.028115</td>\n",
       "      <td>0.025450</td>\n",
       "      <td>0.361204</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27</td>\n",
       "      <td>0.087093</td>\n",
       "      <td>0.048757</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.038821</td>\n",
       "      <td>0.078425</td>\n",
       "      <td>15843</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505382925</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002433</td>\n",
       "      <td>0.004444</td>\n",
       "      <td>0.001776</td>\n",
       "      <td>-0.434799</td>\n",
       "      <td>-0.213637</td>\n",
       "      <td>0.070131</td>\n",
       "      <td>0.065893</td>\n",
       "      <td>0.051810</td>\n",
       "      <td>True</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28</td>\n",
       "      <td>0.007613</td>\n",
       "      <td>0.004868</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.004017</td>\n",
       "      <td>0.005051</td>\n",
       "      <td>15845</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505525476</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002298</td>\n",
       "      <td>0.004425</td>\n",
       "      <td>0.001657</td>\n",
       "      <td>-0.403051</td>\n",
       "      <td>-0.217065</td>\n",
       "      <td>0.004179</td>\n",
       "      <td>0.005651</td>\n",
       "      <td>0.540157</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29</td>\n",
       "      <td>0.005679</td>\n",
       "      <td>0.003001</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.002306</td>\n",
       "      <td>0.004566</td>\n",
       "      <td>15849</td>\n",
       "      <td>dryer</td>\n",
       "      <td>17817263559</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.002181</td>\n",
       "      <td>0.004405</td>\n",
       "      <td>0.001557</td>\n",
       "      <td>-0.370219</td>\n",
       "      <td>-0.218479</td>\n",
       "      <td>0.004062</td>\n",
       "      <td>0.004568</td>\n",
       "      <td>0.616029</td>\n",
       "      <td>False</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>30 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    rank  dest_ctr_mean  dest_ctr_std  dest_rank  dest_ctr_mad  \\\n",
       "0      0       0.005123      0.002433       27.0      0.001776   \n",
       "1      1       0.006111      0.003424       22.0      0.002703   \n",
       "2      2       0.031860      0.019346        6.0      0.015932   \n",
       "3      3       0.004872      0.002181       29.0      0.001557   \n",
       "4      4       0.008799      0.005915       16.0      0.005005   \n",
       "5      5       0.011451      0.008090       13.0      0.007052   \n",
       "6      6       0.004991      0.002298       28.0      0.001657   \n",
       "7      7       0.009547      0.006554       15.0      0.005619   \n",
       "8      8       0.006741      0.004042       20.0      0.003251   \n",
       "9      9       0.012683      0.009012       12.0      0.007897   \n",
       "10    10       0.215521      0.172469        0.0      0.124872   \n",
       "11    11       0.005460      0.002820       25.0      0.002111   \n",
       "12    12       0.006423      0.003708       21.0      0.002963   \n",
       "13    13       0.065306      0.036182        3.0      0.028924   \n",
       "14    14       0.041020      0.024764        5.0      0.019993   \n",
       "15    15       0.005897      0.003219       23.0      0.002507   \n",
       "16    16       0.018520      0.012575        9.0      0.010934   \n",
       "17    17       0.005286      0.002583       26.0      0.001923   \n",
       "18    18       0.121488      0.070496        1.0      0.054231   \n",
       "19    19       0.026049      0.016632        7.0      0.013968   \n",
       "20    20       0.021772      0.014593        8.0      0.012446   \n",
       "21    21       0.010445      0.007291       14.0      0.006331   \n",
       "22    22       0.008103      0.005321       17.0      0.004453   \n",
       "23    23       0.051849      0.030129        4.0      0.024117   \n",
       "24    24       0.016110      0.011305       10.0      0.009929   \n",
       "25    25       0.007177      0.004475       19.0      0.003650   \n",
       "26    26       0.014300      0.010128       11.0      0.008936   \n",
       "27    27       0.087093      0.048757        2.0      0.038821   \n",
       "28    28       0.007613      0.004868       18.0      0.004017   \n",
       "29    29       0.005679      0.003001       24.0      0.002306   \n",
       "\n",
       "    dest_ctr_median  index  query clicked_doc_id  click_count  ...  \\\n",
       "0          0.004444  15844  dryer    12505451713           20  ...   \n",
       "1          0.004651  15893  dryer   883929085118           18  ...   \n",
       "2          0.031660  15887  dryer   883049066905           16  ...   \n",
       "3          0.004405  15854  dryer    36172950027           13  ...   \n",
       "4          0.005435  15870  dryer    74108056764           13  ...   \n",
       "5          0.008439  15872  dryer    77283045400           13  ...   \n",
       "6          0.004425  15883  dryer   783722274422           13  ...   \n",
       "7          0.006536  15880  dryer   665331101927           11  ...   \n",
       "8          0.004808  15848  dryer    14381196320            9  ...   \n",
       "9          0.009276  15871  dryer    74108096487            9  ...   \n",
       "10         0.158897  15886  dryer   856751002097            9  ...   \n",
       "11         0.004505  15846  dryer    12505525766            8  ...   \n",
       "12         0.004717  15900  dryer   048231011402            8  ...   \n",
       "13         0.059932  15901  dryer   074108007469            7  ...   \n",
       "14         0.039578  15865  dryer    48231011396            6  ...   \n",
       "15         0.004608  15857  dryer    36725561977            4  ...   \n",
       "16         0.017204  15860  dryer    36725578241            4  ...   \n",
       "17         0.004484  15847  dryer    12505527456            3  ...   \n",
       "18         0.109649  15874  dryer    84691226703            3  ...   \n",
       "19         0.025751  15875  dryer    84691226727            3  ...   \n",
       "20         0.020859  15876  dryer    85391137726            3  ...   \n",
       "21         0.007313  15856  dryer    36725560789            2  ...   \n",
       "22         0.005128  15859  dryer    36725578142            2  ...   \n",
       "23         0.048343  15864  dryer    48231011198            2  ...   \n",
       "24         0.013758  15881  dryer   723755811416            2  ...   \n",
       "25         0.004902  15891  dryer   883049199061            2  ...   \n",
       "26         0.011174  15904  dryer   883049200057            2  ...   \n",
       "27         0.078425  15843  dryer    12505382925            1  ...   \n",
       "28         0.005051  15845  dryer    12505525476            1  ...   \n",
       "29         0.004566  15849  dryer    17817263559            1  ...   \n",
       "\n",
       "    posn_ctr_std  posn_ctr_median  posn_ctr_mad  ctr_std_z_score  \\\n",
       "0       0.172469         0.158897      0.124872        -0.778227   \n",
       "1       0.070496         0.109649      0.054231        -0.685393   \n",
       "2       0.048757         0.078425      0.038821        -0.452290   \n",
       "3       0.036182         0.059932      0.028924        -0.344385   \n",
       "4       0.030129         0.048343      0.024117         0.033090   \n",
       "5       0.024764         0.039578      0.019993         0.477524   \n",
       "6       0.019346         0.031660      0.015932         1.084741   \n",
       "7       0.016632         0.025751      0.013968         1.122345   \n",
       "8       0.014593         0.020859      0.012446         1.015130   \n",
       "9       0.012575         0.017204      0.010934         1.436546   \n",
       "10      0.011305         0.013758      0.009929         1.811274   \n",
       "11      0.010128         0.011174      0.008936         1.799011   \n",
       "12      0.009012         0.009276      0.007897         2.201221   \n",
       "13      0.008090         0.008439      0.007052         2.101933   \n",
       "14      0.007291         0.007313      0.006331         1.912821   \n",
       "15      0.006554         0.006536      0.005619         1.024235   \n",
       "16      0.005915         0.005435      0.005005         1.261551   \n",
       "17      0.005321         0.005128      0.004453         0.769038   \n",
       "18      0.004868         0.005051      0.004017         0.941312   \n",
       "19      0.004475         0.004902      0.003650         1.121460   \n",
       "20      0.004042         0.004808      0.003251         1.349483   \n",
       "21      0.003708         0.004717      0.002963         0.460480   \n",
       "22      0.003424         0.004651      0.002703         0.589684   \n",
       "23      0.003219         0.004608      0.002507         0.693542   \n",
       "24      0.003001         0.004566      0.002306         0.816731   \n",
       "25      0.002820         0.004505      0.002111         0.946966   \n",
       "26      0.002583         0.004484      0.001923         1.100956   \n",
       "27      0.002433         0.004444      0.001776        -0.434799   \n",
       "28      0.002298         0.004425      0.001657        -0.403051   \n",
       "29      0.002181         0.004405      0.001557        -0.370219   \n",
       "\n",
       "    ctr_mod_z_score  dest_ctr_median_based  dest_ctr_mean_based      draw  \\\n",
       "0         -0.621404               0.003341             0.003229  0.664851   \n",
       "1         -0.672647               0.002833             0.003764  0.100723   \n",
       "2         -0.344763               0.026167             0.023110  0.749103   \n",
       "3         -0.244987               0.004024             0.004121  0.506594   \n",
       "4          0.186711               0.006369             0.008994  0.645359   \n",
       "5          0.663618               0.013118             0.015314  0.938898   \n",
       "6          1.329765               0.006629             0.007484  0.588894   \n",
       "7          1.357717               0.014166             0.016903  0.126852   \n",
       "8          1.263529               0.008916             0.010844  0.622237   \n",
       "9          1.772473               0.023274             0.025629  0.666952   \n",
       "10         2.299050               0.445985             0.527910  0.187322   \n",
       "11         2.388945               0.009548             0.010532  0.599752   \n",
       "12         2.943346               0.013437             0.014585  0.074374   \n",
       "13         2.838598               0.142035             0.141358  0.267837   \n",
       "14         2.697482               0.093509             0.088389  0.372377   \n",
       "15         1.730444               0.008947             0.009195  0.636790   \n",
       "16         2.162826               0.040854             0.034385  0.566257   \n",
       "17         1.586958               0.007536             0.007273  0.386841   \n",
       "18         1.778745               0.206112             0.187846  0.241156   \n",
       "19         1.997980               0.053659             0.044701  0.480969   \n",
       "20         2.272160               0.049139             0.041464  0.987882   \n",
       "21         1.152068               0.014606             0.013802  0.239419   \n",
       "22         1.287277               0.010861             0.011241  0.738529   \n",
       "23         1.404657               0.082219             0.072744  0.577586   \n",
       "24         1.545758               0.029106             0.025342  0.817041   \n",
       "25         1.717253               0.011170             0.011414  0.739095   \n",
       "26         1.895937               0.028115             0.025450  0.361204   \n",
       "27        -0.213637               0.070131             0.065893  0.051810   \n",
       "28        -0.217065               0.004179             0.005651  0.540157   \n",
       "29        -0.218479               0.004062             0.004568  0.616029   \n",
       "\n",
       "    clicked  sess_id  \n",
       "0     False        1  \n",
       "1     False        1  \n",
       "2     False        1  \n",
       "3     False        1  \n",
       "4     False        1  \n",
       "5     False        1  \n",
       "6     False        1  \n",
       "7     False        1  \n",
       "8     False        1  \n",
       "9     False        1  \n",
       "10     True        1  \n",
       "11    False        1  \n",
       "12    False        1  \n",
       "13    False        1  \n",
       "14    False        1  \n",
       "15    False        1  \n",
       "16    False        1  \n",
       "17    False        1  \n",
       "18    False        1  \n",
       "19    False        1  \n",
       "20    False        1  \n",
       "21    False        1  \n",
       "22    False        1  \n",
       "23    False        1  \n",
       "24    False        1  \n",
       "25    False        1  \n",
       "26    False        1  \n",
       "27     True        1  \n",
       "28    False        1  \n",
       "29    False        1  \n",
       "\n",
       "[30 rows x 23 columns]"
      ]
     },
     "execution_count": 181,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "synthesize_session('dryer', 1, baselines)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Randomly sample source signals, generate new sessions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 182,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:28: UserWarning: Boolean Series key will be reindexed to match DataFrame index.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Created Sessions 500 Last Query dryer Elapsed 10.396458700066432\n",
      "Created Sessions 1000 Last Query dryer Elapsed 20.70228330011014\n",
      "Created Sessions 1500 Last Query dryer Elapsed 31.081856100005098\n",
      "Created Sessions 2000 Last Query dryer Elapsed 41.70352660003118\n",
      "Created Sessions 2500 Last Query dryer Elapsed 52.1875013000099\n",
      "Created Sessions 3000 Last Query dryer Elapsed 62.23087950004265\n",
      "Created Sessions 3500 Last Query dryer Elapsed 72.40120730001945\n",
      "Created Sessions 4000 Last Query dryer Elapsed 82.64537190005649\n",
      "Created Sessions 4500 Last Query dryer Elapsed 92.73562240006868\n",
      "Created Sessions 5000 Last Query dryer Elapsed 102.73251730005722\n"
     ]
    }
   ],
   "source": [
    "from time import perf_counter \n",
    "sess_id = 0\n",
    "\n",
    "#for query in QUERIES_TO_SIMULATE:\n",
    "for query in ['dryer']:\n",
    "    \n",
    "    session_dfs=[]\n",
    "    t1_start = perf_counter()  \n",
    "    if len(canonical_rankings[canonical_rankings['query'] == query]) > 0:\n",
    "        for i in range(0, NUM_SESSIONS):\n",
    "            session_dfs.append(synthesize_session(query, sess_id, baselines))\n",
    "            sess_id+=1\n",
    "            if (sess_id % 500 == 0):\n",
    "                print(\"Created Sessions %s Last Query %s Elapsed %s\" % (sess_id, query, perf_counter()-t1_start))\n",
    "    else:\n",
    "        print(\"Query %s not available\" % query)\n",
    "\n",
    "    sessions = pandas.concat(session_dfs)\n",
    "    sessions = sessions.sort_values(['sess_id', 'dest_rank'])\n",
    "    sessions[['sess_id', 'query', 'dest_rank', 'clicked_doc_id', 'clicked']] \\\n",
    "        .rename(columns={'dest_rank': 'rank'}) \\\n",
    "        .to_csv(\"%s_sessions.gz\" % query, compression='gzip', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 183,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sess_id</th>\n",
       "      <th>query</th>\n",
       "      <th>rank</th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>clicked</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>dryer</td>\n",
       "      <td>0.0</td>\n",
       "      <td>36725578241</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>dryer</td>\n",
       "      <td>1.0</td>\n",
       "      <td>84691226703</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>dryer</td>\n",
       "      <td>2.0</td>\n",
       "      <td>12505382925</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>dryer</td>\n",
       "      <td>3.0</td>\n",
       "      <td>74108056764</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>dryer</td>\n",
       "      <td>4.0</td>\n",
       "      <td>48231011198</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99995</th>\n",
       "      <td>59999</td>\n",
       "      <td>blue ray</td>\n",
       "      <td>15.0</td>\n",
       "      <td>23942972389</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99996</th>\n",
       "      <td>59999</td>\n",
       "      <td>blue ray</td>\n",
       "      <td>16.0</td>\n",
       "      <td>786936817232</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99997</th>\n",
       "      <td>59999</td>\n",
       "      <td>blue ray</td>\n",
       "      <td>17.0</td>\n",
       "      <td>36725608443</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99998</th>\n",
       "      <td>59999</td>\n",
       "      <td>blue ray</td>\n",
       "      <td>18.0</td>\n",
       "      <td>36725608511</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>99999</th>\n",
       "      <td>59999</td>\n",
       "      <td>blue ray</td>\n",
       "      <td>19.0</td>\n",
       "      <td>711719804604</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1310000 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       sess_id     query  rank  clicked_doc_id  clicked\n",
       "0            0     dryer   0.0     36725578241     True\n",
       "1            0     dryer   1.0     84691226703    False\n",
       "2            0     dryer   2.0     12505382925     True\n",
       "3            0     dryer   3.0     74108056764    False\n",
       "4            0     dryer   4.0     48231011198    False\n",
       "...        ...       ...   ...             ...      ...\n",
       "99995    59999  blue ray  15.0     23942972389    False\n",
       "99996    59999  blue ray  16.0    786936817232    False\n",
       "99997    59999  blue ray  17.0     36725608443    False\n",
       "99998    59999  blue ray  18.0     36725608511    False\n",
       "99999    59999  blue ray  19.0    711719804604    False\n",
       "\n",
       "[1310000 rows x 5 columns]"
      ]
     },
     "execution_count": 183,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def all_sessions():\n",
    "    import glob\n",
    "    return pandas.concat([pandas.read_csv(f, compression='gzip')\n",
    "                      for f in glob.glob('*_sessions.gz')])\n",
    "\n",
    "sessions = all_sessions()\n",
    "sessions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 184,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>upc</th>\n",
       "      <th>name</th>\n",
       "      <th>manufacturer</th>\n",
       "      <th>short_description</th>\n",
       "      <th>long_description</th>\n",
       "      <th>id</th>\n",
       "      <th>_version_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>883393003458</td>\n",
       "      <td>RCA - 46\" Class - LCD - 1080p - 60Hz - HDTV</td>\n",
       "      <td>RCA</td>\n",
       "      <td>\\N</td>\n",
       "      <td>This HDTV showcases stunning images up to 1080...</td>\n",
       "      <td>953d1c22-88d7-4009-a5f6-b0783a411a11</td>\n",
       "      <td>1684550854019907593</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>843404064434</td>\n",
       "      <td>ZAGG - InvisibleSHIELD for Apple&amp;#xAE; iPhone&amp;...</td>\n",
       "      <td>ZAGG</td>\n",
       "      <td>Compatible with Apple&amp;#xAE; iPhone&amp;#xAE; 4; mi...</td>\n",
       "      <td>Protect your Apple iPhone 4's screen with this...</td>\n",
       "      <td>8355f53e-da62-4667-a86e-9fca0f927ad4</td>\n",
       "      <td>1684550854146785281</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>600603135101</td>\n",
       "      <td>Rocketfish&amp;#x2122; - Protective Cover for NOOK</td>\n",
       "      <td>Rocketfish&amp;#x99;</td>\n",
       "      <td>Compatible with NOOK WiFi and NOOK 3G+WiFi; th...</td>\n",
       "      <td>Protect your NOOK eReader from bumps and scrat...</td>\n",
       "      <td>7346a389-ef33-448e-b7e8-45208226d43b</td>\n",
       "      <td>1684550854148882432</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>813774010904</td>\n",
       "      <td>Samsung - Refurbished Wi-Fi Ready Blu-ray  Pla...</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>RefurbishedENERGY STAR QualifiedPlays DVD and ...</td>\n",
       "      <td>See movies come to life in brilliant high-defi...</td>\n",
       "      <td>fc81cd8c-376f-4f66-b4a2-59101009587f</td>\n",
       "      <td>1684550854156222466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>97278016000</td>\n",
       "      <td>Furuno 600l Color LCD Fishfinder - DVD</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>db3c0349-594f-4e89-85e3-8ceef1dc0494</td>\n",
       "      <td>1684550854261080072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>228</th>\n",
       "      <td>30206696622</td>\n",
       "      <td>Star Trek [Music from the Motion Picture] - Or...</td>\n",
       "      <td>Var&amp;#xBF;se Sarabande (USA)</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>572370c6-9cde-4052-9cf0-e7ba144cef05</td>\n",
       "      <td>1684550854608158729</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>229</th>\n",
       "      <td>30206696622</td>\n",
       "      <td>Star Trek (Score) - Original Soundtrack - CD</td>\n",
       "      <td>Var&amp;#xBF;se Sarabande (USA)</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>1340079b-9d51-4664-862b-f33417f03742</td>\n",
       "      <td>1684550855913635852</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230</th>\n",
       "      <td>786936817232</td>\n",
       "      <td>Pirates Of The Caribbean: On Stranger Tides (2...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>fc0243b3-74aa-4625-aea3-8f62374046e8</td>\n",
       "      <td>1684550857360670725</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>231</th>\n",
       "      <td>24543742180</td>\n",
       "      <td>Star Wars: The Complete Saga [9 Discs / Blu-ra...</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>83071377-bcd3-44c3-a990-55aafeea01a2</td>\n",
       "      <td>1684550857673146372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>232</th>\n",
       "      <td>786936805017</td>\n",
       "      <td>Disney's A Christmas Carol - Blu-ray Disc</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>c0317ba8-6e66-4c18-8fa8-ebd019626fad</td>\n",
       "      <td>1684550857830432773</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>233 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              upc                                               name  \\\n",
       "0    883393003458        RCA - 46\" Class - LCD - 1080p - 60Hz - HDTV   \n",
       "1    843404064434  ZAGG - InvisibleSHIELD for Apple&#xAE; iPhone&...   \n",
       "2    600603135101     Rocketfish&#x2122; - Protective Cover for NOOK   \n",
       "3    813774010904  Samsung - Refurbished Wi-Fi Ready Blu-ray  Pla...   \n",
       "4     97278016000             Furuno 600l Color LCD Fishfinder - DVD   \n",
       "..            ...                                                ...   \n",
       "228   30206696622  Star Trek [Music from the Motion Picture] - Or...   \n",
       "229   30206696622       Star Trek (Score) - Original Soundtrack - CD   \n",
       "230  786936817232  Pirates Of The Caribbean: On Stranger Tides (2...   \n",
       "231   24543742180  Star Wars: The Complete Saga [9 Discs / Blu-ra...   \n",
       "232  786936805017          Disney's A Christmas Carol - Blu-ray Disc   \n",
       "\n",
       "                    manufacturer  \\\n",
       "0                            RCA   \n",
       "1                           ZAGG   \n",
       "2               Rocketfish&#x99;   \n",
       "3                        Samsung   \n",
       "4                             \\N   \n",
       "..                           ...   \n",
       "228  Var&#xBF;se Sarabande (USA)   \n",
       "229  Var&#xBF;se Sarabande (USA)   \n",
       "230                           \\N   \n",
       "231                           \\N   \n",
       "232                           \\N   \n",
       "\n",
       "                                      short_description  \\\n",
       "0                                                   \\N   \n",
       "1    Compatible with Apple&#xAE; iPhone&#xAE; 4; mi...   \n",
       "2    Compatible with NOOK WiFi and NOOK 3G+WiFi; th...   \n",
       "3    RefurbishedENERGY STAR QualifiedPlays DVD and ...   \n",
       "4                                                   \\N   \n",
       "..                                                 ...   \n",
       "228                                                 \\N   \n",
       "229                                                 \\N   \n",
       "230                                                 \\N   \n",
       "231                                                 \\N   \n",
       "232                                                 \\N   \n",
       "\n",
       "                                       long_description  \\\n",
       "0    This HDTV showcases stunning images up to 1080...   \n",
       "1    Protect your Apple iPhone 4's screen with this...   \n",
       "2    Protect your NOOK eReader from bumps and scrat...   \n",
       "3    See movies come to life in brilliant high-defi...   \n",
       "4                                                   \\N   \n",
       "..                                                 ...   \n",
       "228                                                 \\N   \n",
       "229                                                 \\N   \n",
       "230                                                 \\N   \n",
       "231                                                 \\N   \n",
       "232                                                 \\N   \n",
       "\n",
       "                                       id            _version_  \n",
       "0    953d1c22-88d7-4009-a5f6-b0783a411a11  1684550854019907593  \n",
       "1    8355f53e-da62-4667-a86e-9fca0f927ad4  1684550854146785281  \n",
       "2    7346a389-ef33-448e-b7e8-45208226d43b  1684550854148882432  \n",
       "3    fc81cd8c-376f-4f66-b4a2-59101009587f  1684550854156222466  \n",
       "4    db3c0349-594f-4e89-85e3-8ceef1dc0494  1684550854261080072  \n",
       "..                                    ...                  ...  \n",
       "228  572370c6-9cde-4052-9cf0-e7ba144cef05  1684550854608158729  \n",
       "229  1340079b-9d51-4664-862b-f33417f03742  1684550855913635852  \n",
       "230  fc0243b3-74aa-4625-aea3-8f62374046e8  1684550857360670725  \n",
       "231  83071377-bcd3-44c3-a990-55aafeea01a2  1684550857673146372  \n",
       "232  c0317ba8-6e66-4c18-8fa8-ebd019626fad  1684550857830432773  \n",
       "\n",
       "[233 rows x 7 columns]"
      ]
     },
     "execution_count": 184,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def fetch_products(doc_ids):\n",
    "    import requests\n",
    "    doc_ids = [\"%s\" % doc_id for doc_id in doc_ids]\n",
    "    query = \"upc:( \" + \" OR \".join(doc_ids) + \" )\"\n",
    "    params = {'q':  query, 'wt': 'json', 'rows': len(doc_ids)}\n",
    "    resp = requests.get('http://aips-solr:8983/solr/products/select', params=params)\n",
    "    df = pandas.DataFrame(resp.json()['response']['docs'])\n",
    "    df['upc'] = df['upc'].astype('int64')\n",
    "    return df\n",
    "\n",
    "products = fetch_products(doc_ids=sessions['clicked_doc_id'].unique())\n",
    "products"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 185,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>clicked</th>\n",
       "      <th>ctr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>74108007469</td>\n",
       "      <td>1805.0</td>\n",
       "      <td>0.3610</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>84691226703</td>\n",
       "      <td>624.0</td>\n",
       "      <td>0.1248</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>48231011402</td>\n",
       "      <td>615.0</td>\n",
       "      <td>0.1230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>48231011396</td>\n",
       "      <td>405.0</td>\n",
       "      <td>0.0810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>48231011198</td>\n",
       "      <td>391.0</td>\n",
       "      <td>0.0782</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>12505382925</td>\n",
       "      <td>333.0</td>\n",
       "      <td>0.0666</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>665331101927</td>\n",
       "      <td>250.0</td>\n",
       "      <td>0.0500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>36725578241</td>\n",
       "      <td>219.0</td>\n",
       "      <td>0.0438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>85391137726</td>\n",
       "      <td>214.0</td>\n",
       "      <td>0.0428</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>84691226727</td>\n",
       "      <td>196.0</td>\n",
       "      <td>0.0392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12505525766</td>\n",
       "      <td>184.0</td>\n",
       "      <td>0.0368</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>856751002097</td>\n",
       "      <td>183.0</td>\n",
       "      <td>0.0366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>74108096487</td>\n",
       "      <td>156.0</td>\n",
       "      <td>0.0312</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>783722274422</td>\n",
       "      <td>143.0</td>\n",
       "      <td>0.0286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>36725561977</td>\n",
       "      <td>137.0</td>\n",
       "      <td>0.0274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>14381196320</td>\n",
       "      <td>136.0</td>\n",
       "      <td>0.0272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>723755811416</td>\n",
       "      <td>119.0</td>\n",
       "      <td>0.0238</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>883049066905</td>\n",
       "      <td>118.0</td>\n",
       "      <td>0.0236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>883049200057</td>\n",
       "      <td>114.0</td>\n",
       "      <td>0.0228</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>77283045400</td>\n",
       "      <td>112.0</td>\n",
       "      <td>0.0224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>12505527456</td>\n",
       "      <td>111.0</td>\n",
       "      <td>0.0222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>36725560789</td>\n",
       "      <td>79.0</td>\n",
       "      <td>0.0158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>74108056764</td>\n",
       "      <td>79.0</td>\n",
       "      <td>0.0158</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>36172950027</td>\n",
       "      <td>56.0</td>\n",
       "      <td>0.0112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>36725578142</td>\n",
       "      <td>52.0</td>\n",
       "      <td>0.0104</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>883049199061</td>\n",
       "      <td>46.0</td>\n",
       "      <td>0.0092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>12505451713</td>\n",
       "      <td>42.0</td>\n",
       "      <td>0.0084</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>883929085118</td>\n",
       "      <td>36.0</td>\n",
       "      <td>0.0072</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>12505525476</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.0048</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>17817263559</td>\n",
       "      <td>21.0</td>\n",
       "      <td>0.0042</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    clicked_doc_id  clicked     ctr\n",
       "15     74108007469   1805.0  0.3610\n",
       "19     84691226703    624.0  0.1248\n",
       "14     48231011402    615.0  0.1230\n",
       "13     48231011396    405.0  0.0810\n",
       "12     48231011198    391.0  0.0782\n",
       "0      12505382925    333.0  0.0666\n",
       "22    665331101927    250.0  0.0500\n",
       "11     36725578241    219.0  0.0438\n",
       "21     85391137726    214.0  0.0428\n",
       "20     84691226727    196.0  0.0392\n",
       "3      12505525766    184.0  0.0368\n",
       "25    856751002097    183.0  0.0366\n",
       "17     74108096487    156.0  0.0312\n",
       "24    783722274422    143.0  0.0286\n",
       "9      36725561977    137.0  0.0274\n",
       "5      14381196320    136.0  0.0272\n",
       "23    723755811416    119.0  0.0238\n",
       "26    883049066905    118.0  0.0236\n",
       "28    883049200057    114.0  0.0228\n",
       "18     77283045400    112.0  0.0224\n",
       "4      12505527456    111.0  0.0222\n",
       "8      36725560789     79.0  0.0158\n",
       "16     74108056764     79.0  0.0158\n",
       "7      36172950027     56.0  0.0112\n",
       "10     36725578142     52.0  0.0104\n",
       "27    883049199061     46.0  0.0092\n",
       "1      12505451713     42.0  0.0084\n",
       "29    883929085118     36.0  0.0072\n",
       "2      12505525476     24.0  0.0048\n",
       "6      17817263559     21.0  0.0042"
      ]
     },
     "execution_count": 185,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#dryer_sessions = dryer_sessions.merge(dryer_products, left_on='clicked_doc_id', right_on='upc', how='left')\n",
    "dryer_sessions=sessions[sessions['query'] == 'dryer']\n",
    "\n",
    "num_sessions = len(dryer_sessions['sess_id'].unique())\n",
    "dryer_ctrs = dryer_sessions.groupby('clicked_doc_id')['clicked'].sum().reset_index()\n",
    "dryer_ctrs['ctr'] = dryer_ctrs['clicked'] / num_sessions\n",
    "#dryer_ctrs = dryer_ctrs.sort_values('clicked', ascending=False)\\\n",
    "#                       .merge(products, left_on='clicked_doc_id', right_on='upc', how='left')\n",
    "#dryer_sessions.groupby('clicked_doc_id').sum().sort_values('clicked', ascending=False)\n",
    "\n",
    "dryer_ctrs.sort_values('ctr', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 186,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>query</th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>click_count</th>\n",
       "      <th>tot_query_count</th>\n",
       "      <th>ctr</th>\n",
       "      <th>rank</th>\n",
       "      <th>posn_ctr_mean</th>\n",
       "      <th>posn_ctr_std</th>\n",
       "      <th>posn_ctr_median</th>\n",
       "      <th>posn_ctr_mad</th>\n",
       "      <th>ctr_std_z_score</th>\n",
       "      <th>ctr_mod_z_score</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15580</th>\n",
       "      <td>15844</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505451713</td>\n",
       "      <td>20</td>\n",
       "      <td>246</td>\n",
       "      <td>0.081301</td>\n",
       "      <td>0</td>\n",
       "      <td>0.215521</td>\n",
       "      <td>0.172469</td>\n",
       "      <td>0.158897</td>\n",
       "      <td>0.124872</td>\n",
       "      <td>-0.778227</td>\n",
       "      <td>-0.621404</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15581</th>\n",
       "      <td>15893</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883929085118</td>\n",
       "      <td>18</td>\n",
       "      <td>246</td>\n",
       "      <td>0.073171</td>\n",
       "      <td>1</td>\n",
       "      <td>0.121488</td>\n",
       "      <td>0.070496</td>\n",
       "      <td>0.109649</td>\n",
       "      <td>0.054231</td>\n",
       "      <td>-0.685393</td>\n",
       "      <td>-0.672647</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15582</th>\n",
       "      <td>15887</td>\n",
       "      <td>dryer</td>\n",
       "      <td>883049066905</td>\n",
       "      <td>16</td>\n",
       "      <td>246</td>\n",
       "      <td>0.065041</td>\n",
       "      <td>2</td>\n",
       "      <td>0.087093</td>\n",
       "      <td>0.048757</td>\n",
       "      <td>0.078425</td>\n",
       "      <td>0.038821</td>\n",
       "      <td>-0.452290</td>\n",
       "      <td>-0.344763</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15583</th>\n",
       "      <td>15854</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36172950027</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>3</td>\n",
       "      <td>0.065306</td>\n",
       "      <td>0.036182</td>\n",
       "      <td>0.059932</td>\n",
       "      <td>0.028924</td>\n",
       "      <td>-0.344385</td>\n",
       "      <td>-0.244987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15584</th>\n",
       "      <td>15870</td>\n",
       "      <td>dryer</td>\n",
       "      <td>74108056764</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>4</td>\n",
       "      <td>0.051849</td>\n",
       "      <td>0.030129</td>\n",
       "      <td>0.048343</td>\n",
       "      <td>0.024117</td>\n",
       "      <td>0.033090</td>\n",
       "      <td>0.186711</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15585</th>\n",
       "      <td>15872</td>\n",
       "      <td>dryer</td>\n",
       "      <td>77283045400</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>5</td>\n",
       "      <td>0.041020</td>\n",
       "      <td>0.024764</td>\n",
       "      <td>0.039578</td>\n",
       "      <td>0.019993</td>\n",
       "      <td>0.477524</td>\n",
       "      <td>0.663618</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15586</th>\n",
       "      <td>15883</td>\n",
       "      <td>dryer</td>\n",
       "      <td>783722274422</td>\n",
       "      <td>13</td>\n",
       "      <td>246</td>\n",
       "      <td>0.052846</td>\n",
       "      <td>6</td>\n",
       "      <td>0.031860</td>\n",
       "      <td>0.019346</td>\n",
       "      <td>0.031660</td>\n",
       "      <td>0.015932</td>\n",
       "      <td>1.084741</td>\n",
       "      <td>1.329765</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15587</th>\n",
       "      <td>15880</td>\n",
       "      <td>dryer</td>\n",
       "      <td>665331101927</td>\n",
       "      <td>11</td>\n",
       "      <td>246</td>\n",
       "      <td>0.044715</td>\n",
       "      <td>7</td>\n",
       "      <td>0.026049</td>\n",
       "      <td>0.016632</td>\n",
       "      <td>0.025751</td>\n",
       "      <td>0.013968</td>\n",
       "      <td>1.122345</td>\n",
       "      <td>1.357717</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15589</th>\n",
       "      <td>15871</td>\n",
       "      <td>dryer</td>\n",
       "      <td>74108096487</td>\n",
       "      <td>9</td>\n",
       "      <td>246</td>\n",
       "      <td>0.036585</td>\n",
       "      <td>9</td>\n",
       "      <td>0.018520</td>\n",
       "      <td>0.012575</td>\n",
       "      <td>0.017204</td>\n",
       "      <td>0.010934</td>\n",
       "      <td>1.436546</td>\n",
       "      <td>1.772473</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15590</th>\n",
       "      <td>15886</td>\n",
       "      <td>dryer</td>\n",
       "      <td>856751002097</td>\n",
       "      <td>9</td>\n",
       "      <td>246</td>\n",
       "      <td>0.036585</td>\n",
       "      <td>10</td>\n",
       "      <td>0.016110</td>\n",
       "      <td>0.011305</td>\n",
       "      <td>0.013758</td>\n",
       "      <td>0.009929</td>\n",
       "      <td>1.811274</td>\n",
       "      <td>2.299050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15588</th>\n",
       "      <td>15848</td>\n",
       "      <td>dryer</td>\n",
       "      <td>14381196320</td>\n",
       "      <td>9</td>\n",
       "      <td>246</td>\n",
       "      <td>0.036585</td>\n",
       "      <td>8</td>\n",
       "      <td>0.021772</td>\n",
       "      <td>0.014593</td>\n",
       "      <td>0.020859</td>\n",
       "      <td>0.012446</td>\n",
       "      <td>1.015130</td>\n",
       "      <td>1.263529</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15591</th>\n",
       "      <td>15846</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505525766</td>\n",
       "      <td>8</td>\n",
       "      <td>246</td>\n",
       "      <td>0.032520</td>\n",
       "      <td>11</td>\n",
       "      <td>0.014300</td>\n",
       "      <td>0.010128</td>\n",
       "      <td>0.011174</td>\n",
       "      <td>0.008936</td>\n",
       "      <td>1.799011</td>\n",
       "      <td>2.388945</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15592</th>\n",
       "      <td>15900</td>\n",
       "      <td>dryer</td>\n",
       "      <td>048231011402</td>\n",
       "      <td>8</td>\n",
       "      <td>246</td>\n",
       "      <td>0.032520</td>\n",
       "      <td>12</td>\n",
       "      <td>0.012683</td>\n",
       "      <td>0.009012</td>\n",
       "      <td>0.009276</td>\n",
       "      <td>0.007897</td>\n",
       "      <td>2.201221</td>\n",
       "      <td>2.943346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15593</th>\n",
       "      <td>15901</td>\n",
       "      <td>dryer</td>\n",
       "      <td>074108007469</td>\n",
       "      <td>7</td>\n",
       "      <td>246</td>\n",
       "      <td>0.028455</td>\n",
       "      <td>13</td>\n",
       "      <td>0.011451</td>\n",
       "      <td>0.008090</td>\n",
       "      <td>0.008439</td>\n",
       "      <td>0.007052</td>\n",
       "      <td>2.101933</td>\n",
       "      <td>2.838598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15594</th>\n",
       "      <td>15865</td>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011396</td>\n",
       "      <td>6</td>\n",
       "      <td>246</td>\n",
       "      <td>0.024390</td>\n",
       "      <td>14</td>\n",
       "      <td>0.010445</td>\n",
       "      <td>0.007291</td>\n",
       "      <td>0.007313</td>\n",
       "      <td>0.006331</td>\n",
       "      <td>1.912821</td>\n",
       "      <td>2.697482</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15595</th>\n",
       "      <td>15857</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725561977</td>\n",
       "      <td>4</td>\n",
       "      <td>246</td>\n",
       "      <td>0.016260</td>\n",
       "      <td>15</td>\n",
       "      <td>0.009547</td>\n",
       "      <td>0.006554</td>\n",
       "      <td>0.006536</td>\n",
       "      <td>0.005619</td>\n",
       "      <td>1.024235</td>\n",
       "      <td>1.730444</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15596</th>\n",
       "      <td>15860</td>\n",
       "      <td>dryer</td>\n",
       "      <td>36725578241</td>\n",
       "      <td>4</td>\n",
       "      <td>246</td>\n",
       "      <td>0.016260</td>\n",
       "      <td>16</td>\n",
       "      <td>0.008799</td>\n",
       "      <td>0.005915</td>\n",
       "      <td>0.005435</td>\n",
       "      <td>0.005005</td>\n",
       "      <td>1.261551</td>\n",
       "      <td>2.162826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15597</th>\n",
       "      <td>15847</td>\n",
       "      <td>dryer</td>\n",
       "      <td>12505527456</td>\n",
       "      <td>3</td>\n",
       "      <td>246</td>\n",
       "      <td>0.012195</td>\n",
       "      <td>17</td>\n",
       "      <td>0.008103</td>\n",
       "      <td>0.005321</td>\n",
       "      <td>0.005128</td>\n",
       "      <td>0.004453</td>\n",
       "      <td>0.769038</td>\n",
       "      <td>1.586958</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15598</th>\n",
       "      <td>15874</td>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226703</td>\n",
       "      <td>3</td>\n",
       "      <td>246</td>\n",
       "      <td>0.012195</td>\n",
       "      <td>18</td>\n",
       "      <td>0.007613</td>\n",
       "      <td>0.004868</td>\n",
       "      <td>0.005051</td>\n",
       "      <td>0.004017</td>\n",
       "      <td>0.941312</td>\n",
       "      <td>1.778745</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15599</th>\n",
       "      <td>15875</td>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226727</td>\n",
       "      <td>3</td>\n",
       "      <td>246</td>\n",
       "      <td>0.012195</td>\n",
       "      <td>19</td>\n",
       "      <td>0.007177</td>\n",
       "      <td>0.004475</td>\n",
       "      <td>0.004902</td>\n",
       "      <td>0.003650</td>\n",
       "      <td>1.121460</td>\n",
       "      <td>1.997980</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       index  query clicked_doc_id  click_count  tot_query_count       ctr  \\\n",
       "15580  15844  dryer    12505451713           20              246  0.081301   \n",
       "15581  15893  dryer   883929085118           18              246  0.073171   \n",
       "15582  15887  dryer   883049066905           16              246  0.065041   \n",
       "15583  15854  dryer    36172950027           13              246  0.052846   \n",
       "15584  15870  dryer    74108056764           13              246  0.052846   \n",
       "15585  15872  dryer    77283045400           13              246  0.052846   \n",
       "15586  15883  dryer   783722274422           13              246  0.052846   \n",
       "15587  15880  dryer   665331101927           11              246  0.044715   \n",
       "15589  15871  dryer    74108096487            9              246  0.036585   \n",
       "15590  15886  dryer   856751002097            9              246  0.036585   \n",
       "15588  15848  dryer    14381196320            9              246  0.036585   \n",
       "15591  15846  dryer    12505525766            8              246  0.032520   \n",
       "15592  15900  dryer   048231011402            8              246  0.032520   \n",
       "15593  15901  dryer   074108007469            7              246  0.028455   \n",
       "15594  15865  dryer    48231011396            6              246  0.024390   \n",
       "15595  15857  dryer    36725561977            4              246  0.016260   \n",
       "15596  15860  dryer    36725578241            4              246  0.016260   \n",
       "15597  15847  dryer    12505527456            3              246  0.012195   \n",
       "15598  15874  dryer    84691226703            3              246  0.012195   \n",
       "15599  15875  dryer    84691226727            3              246  0.012195   \n",
       "\n",
       "       rank  posn_ctr_mean  posn_ctr_std  posn_ctr_median  posn_ctr_mad  \\\n",
       "15580     0       0.215521      0.172469         0.158897      0.124872   \n",
       "15581     1       0.121488      0.070496         0.109649      0.054231   \n",
       "15582     2       0.087093      0.048757         0.078425      0.038821   \n",
       "15583     3       0.065306      0.036182         0.059932      0.028924   \n",
       "15584     4       0.051849      0.030129         0.048343      0.024117   \n",
       "15585     5       0.041020      0.024764         0.039578      0.019993   \n",
       "15586     6       0.031860      0.019346         0.031660      0.015932   \n",
       "15587     7       0.026049      0.016632         0.025751      0.013968   \n",
       "15589     9       0.018520      0.012575         0.017204      0.010934   \n",
       "15590    10       0.016110      0.011305         0.013758      0.009929   \n",
       "15588     8       0.021772      0.014593         0.020859      0.012446   \n",
       "15591    11       0.014300      0.010128         0.011174      0.008936   \n",
       "15592    12       0.012683      0.009012         0.009276      0.007897   \n",
       "15593    13       0.011451      0.008090         0.008439      0.007052   \n",
       "15594    14       0.010445      0.007291         0.007313      0.006331   \n",
       "15595    15       0.009547      0.006554         0.006536      0.005619   \n",
       "15596    16       0.008799      0.005915         0.005435      0.005005   \n",
       "15597    17       0.008103      0.005321         0.005128      0.004453   \n",
       "15598    18       0.007613      0.004868         0.005051      0.004017   \n",
       "15599    19       0.007177      0.004475         0.004902      0.003650   \n",
       "\n",
       "       ctr_std_z_score  ctr_mod_z_score  \n",
       "15580        -0.778227        -0.621404  \n",
       "15581        -0.685393        -0.672647  \n",
       "15582        -0.452290        -0.344763  \n",
       "15583        -0.344385        -0.244987  \n",
       "15584         0.033090         0.186711  \n",
       "15585         0.477524         0.663618  \n",
       "15586         1.084741         1.329765  \n",
       "15587         1.122345         1.357717  \n",
       "15589         1.436546         1.772473  \n",
       "15590         1.811274         2.299050  \n",
       "15588         1.015130         1.263529  \n",
       "15591         1.799011         2.388945  \n",
       "15592         2.201221         2.943346  \n",
       "15593         2.101933         2.838598  \n",
       "15594         1.912821         2.697482  \n",
       "15595         1.024235         1.730444  \n",
       "15596         1.261551         2.162826  \n",
       "15597         0.769038         1.586958  \n",
       "15598         0.941312         1.778745  \n",
       "15599         1.121460         1.997980  "
      ]
     },
     "execution_count": 186,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# As we expect, underperforming (lower z score) for orig rank\n",
    "# generates much lower \"true\" CTR\n",
    "# \n",
    "# whereas overperforming generates a much higher \"true\" CTR\n",
    "# on average\n",
    "orig_dryer.sort_values('ctr', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 187,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>rank</th>\n",
       "      <th>global_ctr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.213000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.116769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>0.079015</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>0.063400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4.0</td>\n",
       "      <td>0.050554</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5.0</td>\n",
       "      <td>0.040523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6.0</td>\n",
       "      <td>0.029200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7.0</td>\n",
       "      <td>0.024569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>8.0</td>\n",
       "      <td>0.021046</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>9.0</td>\n",
       "      <td>0.017908</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>10.0</td>\n",
       "      <td>0.014831</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>11.0</td>\n",
       "      <td>0.013292</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>12.0</td>\n",
       "      <td>0.011938</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>13.0</td>\n",
       "      <td>0.010415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>14.0</td>\n",
       "      <td>0.009585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>15.0</td>\n",
       "      <td>0.007862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>16.0</td>\n",
       "      <td>0.007600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>17.0</td>\n",
       "      <td>0.006985</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>18.0</td>\n",
       "      <td>0.006077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>19.0</td>\n",
       "      <td>0.006138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>20.0</td>\n",
       "      <td>0.000723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>21.0</td>\n",
       "      <td>0.000785</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>22.0</td>\n",
       "      <td>0.000385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>23.0</td>\n",
       "      <td>0.000615</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>24.0</td>\n",
       "      <td>0.000323</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>25.0</td>\n",
       "      <td>0.000800</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>26.0</td>\n",
       "      <td>0.000385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>27.0</td>\n",
       "      <td>0.000538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>28.0</td>\n",
       "      <td>0.000723</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>29.0</td>\n",
       "      <td>0.000308</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    rank  global_ctr\n",
       "0    0.0    0.213000\n",
       "1    1.0    0.116769\n",
       "2    2.0    0.079015\n",
       "3    3.0    0.063400\n",
       "4    4.0    0.050554\n",
       "5    5.0    0.040523\n",
       "6    6.0    0.029200\n",
       "7    7.0    0.024569\n",
       "8    8.0    0.021046\n",
       "9    9.0    0.017908\n",
       "10  10.0    0.014831\n",
       "11  11.0    0.013292\n",
       "12  12.0    0.011938\n",
       "13  13.0    0.010415\n",
       "14  14.0    0.009585\n",
       "15  15.0    0.007862\n",
       "16  16.0    0.007600\n",
       "17  17.0    0.006985\n",
       "18  18.0    0.006077\n",
       "19  19.0    0.006138\n",
       "20  20.0    0.000723\n",
       "21  21.0    0.000785\n",
       "22  22.0    0.000385\n",
       "23  23.0    0.000615\n",
       "24  24.0    0.000323\n",
       "25  25.0    0.000800\n",
       "26  26.0    0.000385\n",
       "27  27.0    0.000538\n",
       "28  28.0    0.000723\n",
       "29  29.0    0.000308"
      ]
     },
     "execution_count": 187,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_sessions = len(sessions['sess_id'].unique())\n",
    "global_ctrs = sessions.groupby('rank')['clicked'].sum() / num_sessions\n",
    "global_ctrs = global_ctrs.reset_index().rename(columns={'clicked': 'global_ctr'})\n",
    "global_ctrs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 188,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>query</th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>ctr_over_global</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011402</td>\n",
       "      <td>15.555862</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226727</td>\n",
       "      <td>15.201538</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>dryer</td>\n",
       "      <td>17817263559</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011396</td>\n",
       "      <td>11.347801</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>dryer</td>\n",
       "      <td>12505525766</td>\n",
       "      <td>10.972426</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>dryer</td>\n",
       "      <td>74108007469</td>\n",
       "      <td>9.762923</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>dryer</td>\n",
       "      <td>14381196320</td>\n",
       "      <td>8.544533</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>dryer</td>\n",
       "      <td>12505527456</td>\n",
       "      <td>7.786905</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>dryer</td>\n",
       "      <td>36725578241</td>\n",
       "      <td>7.450465</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>dryer</td>\n",
       "      <td>856751002097</td>\n",
       "      <td>7.219178</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>dryer</td>\n",
       "      <td>74108096487</td>\n",
       "      <td>6.686925</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>dryer</td>\n",
       "      <td>84691226703</td>\n",
       "      <td>6.135262</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>dryer</td>\n",
       "      <td>783722274422</td>\n",
       "      <td>6.023140</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>dryer</td>\n",
       "      <td>36725561977</td>\n",
       "      <td>4.838311</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>dryer</td>\n",
       "      <td>77283045400</td>\n",
       "      <td>3.914683</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>dryer</td>\n",
       "      <td>36172950027</td>\n",
       "      <td>3.488198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>dryer</td>\n",
       "      <td>74108056764</td>\n",
       "      <td>3.436044</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>dryer</td>\n",
       "      <td>665331101927</td>\n",
       "      <td>3.355161</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>dryer</td>\n",
       "      <td>883049066905</td>\n",
       "      <td>3.211274</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>dryer</td>\n",
       "      <td>12505451713</td>\n",
       "      <td>2.076974</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>dryer</td>\n",
       "      <td>85391137726</td>\n",
       "      <td>2.033626</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>dryer</td>\n",
       "      <td>883049200057</td>\n",
       "      <td>1.715278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>dryer</td>\n",
       "      <td>36725560789</td>\n",
       "      <td>1.648475</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>dryer</td>\n",
       "      <td>723755811416</td>\n",
       "      <td>1.604772</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>dryer</td>\n",
       "      <td>48231011198</td>\n",
       "      <td>1.546865</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>dryer</td>\n",
       "      <td>883049199061</td>\n",
       "      <td>1.498747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>dryer</td>\n",
       "      <td>36725578142</td>\n",
       "      <td>1.488987</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>dryer</td>\n",
       "      <td>883929085118</td>\n",
       "      <td>1.453870</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>dryer</td>\n",
       "      <td>12505382925</td>\n",
       "      <td>0.842874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>dryer</td>\n",
       "      <td>12505525476</td>\n",
       "      <td>0.789873</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    query  clicked_doc_id  ctr_over_global\n",
       "14  dryer     48231011402        15.555862\n",
       "20  dryer     84691226727        15.201538\n",
       "6   dryer     17817263559        13.000000\n",
       "13  dryer     48231011396        11.347801\n",
       "3   dryer     12505525766        10.972426\n",
       "15  dryer     74108007469         9.762923\n",
       "5   dryer     14381196320         8.544533\n",
       "4   dryer     12505527456         7.786905\n",
       "11  dryer     36725578241         7.450465\n",
       "25  dryer    856751002097         7.219178\n",
       "17  dryer     74108096487         6.686925\n",
       "19  dryer     84691226703         6.135262\n",
       "24  dryer    783722274422         6.023140\n",
       "9   dryer     36725561977         4.838311\n",
       "18  dryer     77283045400         3.914683\n",
       "7   dryer     36172950027         3.488198\n",
       "16  dryer     74108056764         3.436044\n",
       "22  dryer    665331101927         3.355161\n",
       "26  dryer    883049066905         3.211274\n",
       "1   dryer     12505451713         2.076974\n",
       "21  dryer     85391137726         2.033626\n",
       "28  dryer    883049200057         1.715278\n",
       "8   dryer     36725560789         1.648475\n",
       "23  dryer    723755811416         1.604772\n",
       "12  dryer     48231011198         1.546865\n",
       "27  dryer    883049199061         1.498747\n",
       "10  dryer     36725578142         1.488987\n",
       "29  dryer    883929085118         1.453870\n",
       "0   dryer     12505382925         0.842874\n",
       "2   dryer     12505525476         0.789873"
      ]
     },
     "execution_count": 188,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#coec\n",
    "coec_dryer_sums = dryer_sessions.groupby(['query', 'clicked_doc_id', 'rank'])['clicked'].sum()\n",
    "coec_dryer_counts = dryer_sessions.groupby(['query', 'clicked_doc_id', 'rank'])['clicked'].count()\n",
    "\n",
    "#\n",
    "\n",
    "coecs = coec_dryer_sums / coec_dryer_counts\n",
    "coecs = coecs.reset_index().rename(columns={'clicked': 'ctr_per_posn'})\n",
    "\n",
    "coecs = coecs.merge(global_ctrs, on='rank', how='left')\n",
    "coecs['ctr_over_global'] = coecs['ctr_per_posn'] / coecs['global_ctr']\n",
    "\n",
    "coecs = coecs.groupby(['query', 'clicked_doc_id'])['ctr_over_global'].mean().reset_index()\n",
    "coecs.sort_values('ctr_over_global', ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>clicked_doc_id</th>\n",
       "      <th>clicked</th>\n",
       "      <th>ctr</th>\n",
       "      <th>upc</th>\n",
       "      <th>name</th>\n",
       "      <th>manufacturer</th>\n",
       "      <th>short_description</th>\n",
       "      <th>long_description</th>\n",
       "      <th>id</th>\n",
       "      <th>_version_</th>\n",
       "      <th>global_ctr</th>\n",
       "      <th>coec</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>14381196320</td>\n",
       "      <td>278.0</td>\n",
       "      <td>0.0556</td>\n",
       "      <td>14381196320</td>\n",
       "      <td>The Mind Snatchers - DVD</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>139f9f40-dce1-4692-b357-04a6f072ccbd</td>\n",
       "      <td>1684550856010104835</td>\n",
       "      <td>0.008908</td>\n",
       "      <td>6.241796</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>665331101927</td>\n",
       "      <td>292.0</td>\n",
       "      <td>0.0584</td>\n",
       "      <td>665331101927</td>\n",
       "      <td>Everything in Static - CD</td>\n",
       "      <td>Gig Records (USA)</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>2bff881f-51e4-4845-8e51-e3350849d290</td>\n",
       "      <td>1684550853574262785</td>\n",
       "      <td>0.010492</td>\n",
       "      <td>5.565982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>74108056764</td>\n",
       "      <td>192.0</td>\n",
       "      <td>0.0384</td>\n",
       "      <td>74108056764</td>\n",
       "      <td>Conair - Infiniti Ionic Cord-Keeper Hair Dryer...</td>\n",
       "      <td>Conair</td>\n",
       "      <td>1875 watts; dual voltage; 2 heat and speed set...</td>\n",
       "      <td>With support for dual voltages, this hair drye...</td>\n",
       "      <td>2962da2d-2df5-4bad-abe4-07db1de6113a</td>\n",
       "      <td>1684550857215967237</td>\n",
       "      <td>0.007185</td>\n",
       "      <td>5.344754</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>77283045400</td>\n",
       "      <td>213.0</td>\n",
       "      <td>0.0426</td>\n",
       "      <td>77283045400</td>\n",
       "      <td>Hello Kitty - Hair Dryer - Pink</td>\n",
       "      <td>Hello Kitty</td>\n",
       "      <td>1875 watts of power; high and low heat setting...</td>\n",
       "      <td>This hair dryer delivers 1875 watts of power f...</td>\n",
       "      <td>bf24e2cc-02d9-45c2-99ca-2a8bd592ce78</td>\n",
       "      <td>1684550855886372867</td>\n",
       "      <td>0.008431</td>\n",
       "      <td>5.052920</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>783722274422</td>\n",
       "      <td>298.0</td>\n",
       "      <td>0.0596</td>\n",
       "      <td>783722274422</td>\n",
       "      <td>The Independent - Widescreen Subtitle - DVD</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>fd4edde7-76bc-41ce-b3a7-8dbf89c8e8ab</td>\n",
       "      <td>1684550857037709315</td>\n",
       "      <td>0.013385</td>\n",
       "      <td>4.452874</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>883049066905</td>\n",
       "      <td>130.0</td>\n",
       "      <td>0.0260</td>\n",
       "      <td>883049066905</td>\n",
       "      <td>Whirlpool - Affresh Washer Cleaner</td>\n",
       "      <td>Whirlpool</td>\n",
       "      <td>Package include 3 tablets; removes and prevent...</td>\n",
       "      <td>Keep your washer clean and fresh-smelling with...</td>\n",
       "      <td>cd81acc8-f8e3-4276-9214-07e41e37fe54</td>\n",
       "      <td>1684550854383763465</td>\n",
       "      <td>0.005862</td>\n",
       "      <td>4.435696</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>36172950027</td>\n",
       "      <td>143.0</td>\n",
       "      <td>0.0286</td>\n",
       "      <td>36172950027</td>\n",
       "      <td>Tools in the Dryer: A Rarities Compilation - CD</td>\n",
       "      <td>Merge</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>9c74d95c-ff41-49ae-a27a-6a0e6d7f656f</td>\n",
       "      <td>1684550856238694404</td>\n",
       "      <td>0.006477</td>\n",
       "      <td>4.415677</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>12505527456</td>\n",
       "      <td>332.0</td>\n",
       "      <td>0.0664</td>\n",
       "      <td>12505527456</td>\n",
       "      <td>Smart Choice - 1/2\" Safety+PLUS Stainless-Stee...</td>\n",
       "      <td>Smart Choice</td>\n",
       "      <td>Safety+PLUS automatic shut-off valve; leak det...</td>\n",
       "      <td>This gas dryer connector features an automatic...</td>\n",
       "      <td>6646a49a-645c-4822-ab35-1772d022b1c4</td>\n",
       "      <td>1684550856795488266</td>\n",
       "      <td>0.016062</td>\n",
       "      <td>4.134100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>12505451713</td>\n",
       "      <td>98.0</td>\n",
       "      <td>0.0196</td>\n",
       "      <td>12505451713</td>\n",
       "      <td>Frigidaire - Semi-Rigid Dryer Vent Kit - Silver</td>\n",
       "      <td>Frigidaire</td>\n",
       "      <td>Expandable vent; custom fitted ends and clamps</td>\n",
       "      <td>Enhance drying time with this dryer vent kit t...</td>\n",
       "      <td>4ee4107d-025d-4f24-aa10-b53f765c5d7e</td>\n",
       "      <td>1684550855904198658</td>\n",
       "      <td>0.005369</td>\n",
       "      <td>3.650430</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>84691226727</td>\n",
       "      <td>337.0</td>\n",
       "      <td>0.0674</td>\n",
       "      <td>84691226727</td>\n",
       "      <td>GE - 6.0 Cu. Ft. 3-Cycle Electric Dryer - White</td>\n",
       "      <td>GE</td>\n",
       "      <td>Rotary electromechanical controls; 3 cycles; 3...</td>\n",
       "      <td>This electric dryer features 3 cycles and 3 te...</td>\n",
       "      <td>e10e78d4-02ea-45e4-8086-7f2e842c34af</td>\n",
       "      <td>1684550857733963780</td>\n",
       "      <td>0.020600</td>\n",
       "      <td>3.271845</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>883929085118</td>\n",
       "      <td>82.0</td>\n",
       "      <td>0.0164</td>\n",
       "      <td>883929085118</td>\n",
       "      <td>A Charlie Brown Christmas - AC3 - Blu-ray Disc</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>\\N</td>\n",
       "      <td>540cdd58-99fc-41be-bc51-b414fcdfaff0</td>\n",
       "      <td>1684550856433729540</td>\n",
       "      <td>0.005262</td>\n",
       "      <td>3.116959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>84691226703</td>\n",
       "      <td>338.0</td>\n",
       "      <td>0.0676</td>\n",
       "      <td>84691226703</td>\n",
       "      <td>Hotpoint - 6.0 Cu. Ft. 3-Cycle Electric Dryer ...</td>\n",
       "      <td>Hotpoint</td>\n",
       "      <td>Rotary controls; 3 cycles; 3 temperature setti...</td>\n",
       "      <td>Dry your laundry with this 3-cycle electric dr...</td>\n",
       "      <td>7453481e-f5ce-430b-b6a1-18fa37aba715</td>\n",
       "      <td>1684550854383763466</td>\n",
       "      <td>0.024046</td>\n",
       "      <td>2.811260</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>74108096487</td>\n",
       "      <td>347.0</td>\n",
       "      <td>0.0694</td>\n",
       "      <td>74108096487</td>\n",
       "      <td>Conair - Infiniti Cord-Keeper Professional Tou...</td>\n",
       "      <td>Conair</td>\n",
       "      <td>Tourmaline ceramic technology; ionic technolog...</td>\n",
       "      <td>With tourmaline ceramic technology and ionic t...</td>\n",
       "      <td>e9845afa-5d69-4251-a3cb-4f7c22c48056</td>\n",
       "      <td>1684550857123692544</td>\n",
       "      <td>0.028585</td>\n",
       "      <td>2.427879</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>36725578241</td>\n",
       "      <td>365.0</td>\n",
       "      <td>0.0730</td>\n",
       "      <td>36725578241</td>\n",
       "      <td>Samsung - 7.3 Cu. Ft. 7-Cycle Electric Dryer -...</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>Soft-touch dial controls; 7 preset drying cycl...</td>\n",
       "      <td>This electric dryer features 7 preset cycle op...</td>\n",
       "      <td>ae196167-63c2-43e9-b28f-50b529cc7dc9</td>\n",
       "      <td>1684550854636470283</td>\n",
       "      <td>0.035369</td>\n",
       "      <td>2.063941</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>36725561977</td>\n",
       "      <td>373.0</td>\n",
       "      <td>0.0746</td>\n",
       "      <td>36725561977</td>\n",
       "      <td>Samsung - 3.5 Cu. Ft. 6-Cycle High-Efficiency ...</td>\n",
       "      <td>Samsung</td>\n",
       "      <td>ENERGY STAR QualifiedSoft dial touch pad contr...</td>\n",
       "      <td>Ensure that fabrics from heavy to delicate, ar...</td>\n",
       "      <td>d74f27f5-1bf0-4eb1-853a-b658d7e94fa7</td>\n",
       "      <td>1684550854636470275</td>\n",
       "      <td>0.042000</td>\n",
       "      <td>1.776190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>856751002097</td>\n",
       "      <td>407.0</td>\n",
       "      <td>0.0814</td>\n",
       "      <td>856751002097</td>\n",
       "      <td>Practecol - Dryer Balls (2-Pack)</td>\n",
       "      <td>Practecol</td>\n",
       "      <td>Suitable for use on most dry cycles; reduces l...</td>\n",
       "      <td>These dryer balls are suitable for use on most...</td>\n",
       "      <td>ad71676f-bbd0-4f6e-a796-9508dd1f4e63</td>\n",
       "      <td>1684550857719283714</td>\n",
       "      <td>0.053862</td>\n",
       "      <td>1.511282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>12505525766</td>\n",
       "      <td>435.0</td>\n",
       "      <td>0.0870</td>\n",
       "      <td>12505525766</td>\n",
       "      <td>Smart Choice - 6' 30 Amp 3-Prong Dryer Cord</td>\n",
       "      <td>Smart Choice</td>\n",
       "      <td>Heavy-duty PVC insulation; strain relief safet...</td>\n",
       "      <td>Provides maximum flexibility to prevent kinkin...</td>\n",
       "      <td>e14cd4d3-68a5-4bd0-9c1f-e2c88177ac2b</td>\n",
       "      <td>1684550858205822978</td>\n",
       "      <td>0.066262</td>\n",
       "      <td>1.312979</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>48231011396</td>\n",
       "      <td>441.0</td>\n",
       "      <td>0.0882</td>\n",
       "      <td>48231011396</td>\n",
       "      <td>LG - 3.5 Cu. Ft. 7-Cycle High-Efficiency Washe...</td>\n",
       "      <td>LG</td>\n",
       "      <td>ENERGY STAR QualifiedDigital controls; 7 cycle...</td>\n",
       "      <td>Ensure optimal laundry care with this high-eff...</td>\n",
       "      <td>2d061aa2-65a9-4afd-b8f3-31d3627a6e70</td>\n",
       "      <td>1684550857074409481</td>\n",
       "      <td>0.084169</td>\n",
       "      <td>1.047889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>48231011402</td>\n",
       "      <td>458.0</td>\n",
       "      <td>0.0916</td>\n",
       "      <td>48231011402</td>\n",
       "      <td>LG - 7.1 Cu. Ft. 7-Cycle Electric Dryer - White</td>\n",
       "      <td>LG</td>\n",
       "      <td>Electronic controls with LED display; 7 cycles...</td>\n",
       "      <td>Dry clothes effectively with this electric dry...</td>\n",
       "      <td>aaf567ff-a197-4127-ae97-85fc12c6579b</td>\n",
       "      <td>1684550857074409475</td>\n",
       "      <td>0.120492</td>\n",
       "      <td>0.760215</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>74108007469</td>\n",
       "      <td>463.0</td>\n",
       "      <td>0.0926</td>\n",
       "      <td>74108007469</td>\n",
       "      <td>Conair - 1875-Watt Folding Handle Hair Dryer -...</td>\n",
       "      <td>Conair</td>\n",
       "      <td>2 heat/speed settings; cool shot button; dual ...</td>\n",
       "      <td>Style your hair with ease even on the go with ...</td>\n",
       "      <td>0818d275-8c39-43ee-a239-7348f76c9351</td>\n",
       "      <td>1684550856206188566</td>\n",
       "      <td>0.188308</td>\n",
       "      <td>0.491748</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    clicked_doc_id  clicked     ctr           upc  \\\n",
       "13     14381196320    278.0  0.0556   14381196320   \n",
       "12    665331101927    292.0  0.0584  665331101927   \n",
       "15     74108056764    192.0  0.0384   74108056764   \n",
       "14     77283045400    213.0  0.0426   77283045400   \n",
       "11    783722274422    298.0  0.0596  783722274422   \n",
       "17    883049066905    130.0  0.0260  883049066905   \n",
       "16     36172950027    143.0  0.0286   36172950027   \n",
       "10     12505527456    332.0  0.0664   12505527456   \n",
       "18     12505451713     98.0  0.0196   12505451713   \n",
       "9      84691226727    337.0  0.0674   84691226727   \n",
       "19    883929085118     82.0  0.0164  883929085118   \n",
       "8      84691226703    338.0  0.0676   84691226703   \n",
       "7      74108096487    347.0  0.0694   74108096487   \n",
       "6      36725578241    365.0  0.0730   36725578241   \n",
       "5      36725561977    373.0  0.0746   36725561977   \n",
       "4     856751002097    407.0  0.0814  856751002097   \n",
       "3      12505525766    435.0  0.0870   12505525766   \n",
       "2      48231011396    441.0  0.0882   48231011396   \n",
       "1      48231011402    458.0  0.0916   48231011402   \n",
       "0      74108007469    463.0  0.0926   74108007469   \n",
       "\n",
       "                                                 name       manufacturer  \\\n",
       "13                           The Mind Snatchers - DVD                 \\N   \n",
       "12                          Everything in Static - CD  Gig Records (USA)   \n",
       "15  Conair - Infiniti Ionic Cord-Keeper Hair Dryer...             Conair   \n",
       "14                    Hello Kitty - Hair Dryer - Pink        Hello Kitty   \n",
       "11        The Independent - Widescreen Subtitle - DVD                 \\N   \n",
       "17                 Whirlpool - Affresh Washer Cleaner          Whirlpool   \n",
       "16    Tools in the Dryer: A Rarities Compilation - CD              Merge   \n",
       "10  Smart Choice - 1/2\" Safety+PLUS Stainless-Stee...       Smart Choice   \n",
       "18    Frigidaire - Semi-Rigid Dryer Vent Kit - Silver         Frigidaire   \n",
       "9     GE - 6.0 Cu. Ft. 3-Cycle Electric Dryer - White                 GE   \n",
       "19     A Charlie Brown Christmas - AC3 - Blu-ray Disc                 \\N   \n",
       "8   Hotpoint - 6.0 Cu. Ft. 3-Cycle Electric Dryer ...           Hotpoint   \n",
       "7   Conair - Infiniti Cord-Keeper Professional Tou...             Conair   \n",
       "6   Samsung - 7.3 Cu. Ft. 7-Cycle Electric Dryer -...            Samsung   \n",
       "5   Samsung - 3.5 Cu. Ft. 6-Cycle High-Efficiency ...            Samsung   \n",
       "4                    Practecol - Dryer Balls (2-Pack)          Practecol   \n",
       "3         Smart Choice - 6' 30 Amp 3-Prong Dryer Cord       Smart Choice   \n",
       "2   LG - 3.5 Cu. Ft. 7-Cycle High-Efficiency Washe...                 LG   \n",
       "1     LG - 7.1 Cu. Ft. 7-Cycle Electric Dryer - White                 LG   \n",
       "0   Conair - 1875-Watt Folding Handle Hair Dryer -...             Conair   \n",
       "\n",
       "                                     short_description  \\\n",
       "13                                                 \\N   \n",
       "12                                                 \\N   \n",
       "15  1875 watts; dual voltage; 2 heat and speed set...   \n",
       "14  1875 watts of power; high and low heat setting...   \n",
       "11                                                 \\N   \n",
       "17  Package include 3 tablets; removes and prevent...   \n",
       "16                                                 \\N   \n",
       "10  Safety+PLUS automatic shut-off valve; leak det...   \n",
       "18     Expandable vent; custom fitted ends and clamps   \n",
       "9   Rotary electromechanical controls; 3 cycles; 3...   \n",
       "19                                                 \\N   \n",
       "8   Rotary controls; 3 cycles; 3 temperature setti...   \n",
       "7   Tourmaline ceramic technology; ionic technolog...   \n",
       "6   Soft-touch dial controls; 7 preset drying cycl...   \n",
       "5   ENERGY STAR QualifiedSoft dial touch pad contr...   \n",
       "4   Suitable for use on most dry cycles; reduces l...   \n",
       "3   Heavy-duty PVC insulation; strain relief safet...   \n",
       "2   ENERGY STAR QualifiedDigital controls; 7 cycle...   \n",
       "1   Electronic controls with LED display; 7 cycles...   \n",
       "0   2 heat/speed settings; cool shot button; dual ...   \n",
       "\n",
       "                                      long_description  \\\n",
       "13                                                 \\N   \n",
       "12                                                 \\N   \n",
       "15  With support for dual voltages, this hair drye...   \n",
       "14  This hair dryer delivers 1875 watts of power f...   \n",
       "11                                                 \\N   \n",
       "17  Keep your washer clean and fresh-smelling with...   \n",
       "16                                                 \\N   \n",
       "10  This gas dryer connector features an automatic...   \n",
       "18  Enhance drying time with this dryer vent kit t...   \n",
       "9   This electric dryer features 3 cycles and 3 te...   \n",
       "19                                                 \\N   \n",
       "8   Dry your laundry with this 3-cycle electric dr...   \n",
       "7   With tourmaline ceramic technology and ionic t...   \n",
       "6   This electric dryer features 7 preset cycle op...   \n",
       "5   Ensure that fabrics from heavy to delicate, ar...   \n",
       "4   These dryer balls are suitable for use on most...   \n",
       "3   Provides maximum flexibility to prevent kinkin...   \n",
       "2   Ensure optimal laundry care with this high-eff...   \n",
       "1   Dry clothes effectively with this electric dry...   \n",
       "0   Style your hair with ease even on the go with ...   \n",
       "\n",
       "                                      id            _version_  global_ctr  \\\n",
       "13  139f9f40-dce1-4692-b357-04a6f072ccbd  1684550856010104835    0.008908   \n",
       "12  2bff881f-51e4-4845-8e51-e3350849d290  1684550853574262785    0.010492   \n",
       "15  2962da2d-2df5-4bad-abe4-07db1de6113a  1684550857215967237    0.007185   \n",
       "14  bf24e2cc-02d9-45c2-99ca-2a8bd592ce78  1684550855886372867    0.008431   \n",
       "11  fd4edde7-76bc-41ce-b3a7-8dbf89c8e8ab  1684550857037709315    0.013385   \n",
       "17  cd81acc8-f8e3-4276-9214-07e41e37fe54  1684550854383763465    0.005862   \n",
       "16  9c74d95c-ff41-49ae-a27a-6a0e6d7f656f  1684550856238694404    0.006477   \n",
       "10  6646a49a-645c-4822-ab35-1772d022b1c4  1684550856795488266    0.016062   \n",
       "18  4ee4107d-025d-4f24-aa10-b53f765c5d7e  1684550855904198658    0.005369   \n",
       "9   e10e78d4-02ea-45e4-8086-7f2e842c34af  1684550857733963780    0.020600   \n",
       "19  540cdd58-99fc-41be-bc51-b414fcdfaff0  1684550856433729540    0.005262   \n",
       "8   7453481e-f5ce-430b-b6a1-18fa37aba715  1684550854383763466    0.024046   \n",
       "7   e9845afa-5d69-4251-a3cb-4f7c22c48056  1684550857123692544    0.028585   \n",
       "6   ae196167-63c2-43e9-b28f-50b529cc7dc9  1684550854636470283    0.035369   \n",
       "5   d74f27f5-1bf0-4eb1-853a-b658d7e94fa7  1684550854636470275    0.042000   \n",
       "4   ad71676f-bbd0-4f6e-a796-9508dd1f4e63  1684550857719283714    0.053862   \n",
       "3   e14cd4d3-68a5-4bd0-9c1f-e2c88177ac2b  1684550858205822978    0.066262   \n",
       "2   2d061aa2-65a9-4afd-b8f3-31d3627a6e70  1684550857074409481    0.084169   \n",
       "1   aaf567ff-a197-4127-ae97-85fc12c6579b  1684550857074409475    0.120492   \n",
       "0   0818d275-8c39-43ee-a239-7348f76c9351  1684550856206188566    0.188308   \n",
       "\n",
       "        coec  \n",
       "13  6.241796  \n",
       "12  5.565982  \n",
       "15  5.344754  \n",
       "14  5.052920  \n",
       "11  4.452874  \n",
       "17  4.435696  \n",
       "16  4.415677  \n",
       "10  4.134100  \n",
       "18  3.650430  \n",
       "9   3.271845  \n",
       "19  3.116959  \n",
       "8   2.811260  \n",
       "7   2.427879  \n",
       "6   2.063941  \n",
       "5   1.776190  \n",
       "4   1.511282  \n",
       "3   1.312979  \n",
       "2   1.047889  \n",
       "1   0.760215  \n",
       "0   0.491748  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#dryer_ctrs['global_ctr'] = global_ctrs\n",
    "#dryer_ctrs['coec'] = dryer_ctrs['ctr'] / dryer_ctrs['global_ctr']\n",
    "#dryer_ctrs.sort_values(['coec'], ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
